filtering with set: testuser

Showing posts with label testuser. Show all posts

Monday, March 26, 2012

Grant Permission issue

Hi,
I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
database called as 'Orders'
How do I grant Select, Insert, Update, delete permission to all tables and
views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
How do i grant Execute permission to all stored procedures in the database
'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
Could you please provide SQL statement to achive the above functionality.
Thanks in Advance
-SpskI believe the roles apply to views as well as tables...if so, add TestUser
and OrderUsers to the database roles db_datareader and db_datawriter.
For the procs...try something like this (not fully tested):
select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser], [OrderUsers]'
from sysobjects
where xtype = 'P'
and name not like 'dt_%'
Then copy/paste the resultset in the query pane and run it.
HTH
Jerry
"Spsk" <Spsk@.discussions.microsoft.com> wrote in message
news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
> Hi,
> I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
> database called as 'Orders'
> How do I grant Select, Insert, Update, delete permission to all tables and
> views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
> How do i grant Execute permission to all stored procedures in the database
> 'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
> Could you please provide SQL statement to achive the above functionality.
> Thanks in Advance
> -Spsk|||Thanks for your immediate reply.
Is there any single sql statement /sql block to assign execute permission to
procs.
Best Regards,
Senthil
"Jerry Spivey" wrote:
> I believe the roles apply to views as well as tables...if so, add TestUser
> and OrderUsers to the database roles db_datareader and db_datawriter.
> For the procs...try something like this (not fully tested):
> select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser], [OrderUsers]'
> from sysobjects
> where xtype = 'P'
> and name not like 'dt_%'
> Then copy/paste the resultset in the query pane and run it.
> HTH
> Jerry
>
> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
> news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
> > Hi,
> >
> > I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
> > database called as 'Orders'
> > How do I grant Select, Insert, Update, delete permission to all tables and
> > views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
> >
> > How do i grant Execute permission to all stored procedures in the database
> > 'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
> >
> > Could you please provide SQL statement to achive the above functionality.
> >
> > Thanks in Advance
> >
> > -Spsk
>
>|||Not that I am aware of.
"Spsk" <Spsk@.discussions.microsoft.com> wrote in message
news:1B9A9367-8643-4014-9E22-BC40415DC5CC@.microsoft.com...
> Thanks for your immediate reply.
> Is there any single sql statement /sql block to assign execute permission
> to
> procs.
> Best Regards,
> Senthil
> "Jerry Spivey" wrote:
>> I believe the roles apply to views as well as tables...if so, add
>> TestUser
>> and OrderUsers to the database roles db_datareader and db_datawriter.
>> For the procs...try something like this (not fully tested):
>> select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser],
>> [OrderUsers]'
>> from sysobjects
>> where xtype = 'P'
>> and name not like 'dt_%'
>> Then copy/paste the resultset in the query pane and run it.
>> HTH
>> Jerry
>>
>> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
>> news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
>> > Hi,
>> >
>> > I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and
>> > a
>> > database called as 'Orders'
>> > How do I grant Select, Insert, Update, delete permission to all tables
>> > and
>> > views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
>> >
>> > How do i grant Execute permission to all stored procedures in the
>> > database
>> > 'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
>> >
>> > Could you please provide SQL statement to achive the above
>> > functionality.
>> >
>> > Thanks in Advance
>> >
>> > -Spsk
>>|||Correct. There's none at the moment. You can write some code that loops all users and grants execute
permissions. In 2005, there will be, like:
GRANT EXECUTE ON DATABASE::pubs TO username
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:el50sZvwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Not that I am aware of.
> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
> news:1B9A9367-8643-4014-9E22-BC40415DC5CC@.microsoft.com...
>> Thanks for your immediate reply.
>> Is there any single sql statement /sql block to assign execute permission to
>> procs.
>> Best Regards,
>> Senthil
>> "Jerry Spivey" wrote:
>> I believe the roles apply to views as well as tables...if so, add TestUser
>> and OrderUsers to the database roles db_datareader and db_datawriter.
>> For the procs...try something like this (not fully tested):
>> select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser], [OrderUsers]'
>> from sysobjects
>> where xtype = 'P'
>> and name not like 'dt_%'
>> Then copy/paste the resultset in the query pane and run it.
>> HTH
>> Jerry
>>
>> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
>> news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
>> > Hi,
>> >
>> > I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
>> > database called as 'Orders'
>> > How do I grant Select, Insert, Update, delete permission to all tables and
>> > views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
>> >
>> > How do i grant Execute permission to all stored procedures in the database
>> > 'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
>> >
>> > Could you please provide SQL statement to achive the above functionality.
>> >
>> > Thanks in Advance
>> >
>> > -Spsk
>>
>

Grant Permission issue

Grant Permission

I have one doubt.
I have created one user "Testuser" and it is asssined to public role.
The created user does not have create table permission by default. What
i did was i have granted create table permission to "Testuser" from my
sa login. I have created one table called "permission_test" using
"Testuser". Now "Testuser" become the owner of this table.
i have tried access the table from the Testuser. I can do it.
Now the problem is that if i have tried access the table from my sa
login. i.e
select * from "permission_test" I got the error saying that "Invalid
object name 'permission_test'"
Then i have executed the follwing statment from my testuser
grant select on permission_test to dbo
after this i am getting follwing errors "Invalid object name
'permission_test'"
What should i do to avoid this problem?
My doubt is that,if the owner is different from the DBO, then DBO could
not have any acess to other owners objects?
RegardsUse the 4-part name server.db.owner.object
joe.
"Praveen" <apveen@.gmail.com> wrote in message
news:1127306421.920830.60330@.g14g2000cwa.googlegroups.com...
>I have one doubt.
> I have created one user "Testuser" and it is asssined to public role.
> The created user does not have create table permission by default. What
> i did was i have granted create table permission to "Testuser" from my
> sa login. I have created one table called "permission_test" using
> "Testuser". Now "Testuser" become the owner of this table.
>
> i have tried access the table from the Testuser. I can do it.
>
> Now the problem is that if i have tried access the table from my sa
> login. i.e
> select * from "permission_test" I got the error saying that "Invalid
> object name 'permission_test'"
>
> Then i have executed the follwing statment from my testuser
>
> grant select on permission_test to dbo
>
> after this i am getting follwing errors "Invalid object name
> 'permission_test'"
>
> What should i do to avoid this problem?
>
> My doubt is that,if the owner is different from the DBO, then DBO could
> not have any acess to other owners objects?
>
> Regards
>

Grant Permission

I have one doubt.
I have created one user "Testuser" and it is asssined to public role.
The created user does not have create table permission by default. What
i did was i have granted create table permission to "Testuser" from my
sa login. I have created one table called "permission_test" using
"Testuser". Now "Testuser" become the owner of this table.
i have tried access the table from the Testuser. I can do it.
Now the problem is that if i have tried access the table from my sa
login. i.e
select * from "permission_test" I got the error saying that "Invalid
object name 'permission_test'"
Then i have executed the follwing statment from my testuser
grant select on permission_test to dbo
after this i am getting follwing errors "Invalid object name
'permission_test'"
What should i do to avoid this problem?
My doubt is that,if the owner is different from the DBO, then DBO could
not have any acess to other owners objects?
Regards
Use the 4-part name server.db.owner.object
joe.
"Praveen" <apveen@.gmail.com> wrote in message
news:1127306421.920830.60330@.g14g2000cwa.googlegro ups.com...
>I have one doubt.
> I have created one user "Testuser" and it is asssined to public role.
> The created user does not have create table permission by default. What
> i did was i have granted create table permission to "Testuser" from my
> sa login. I have created one table called "permission_test" using
> "Testuser". Now "Testuser" become the owner of this table.
>
> i have tried access the table from the Testuser. I can do it.
>
> Now the problem is that if i have tried access the table from my sa
> login. i.e
> select * from "permission_test" I got the error saying that "Invalid
> object name 'permission_test'"
>
> Then i have executed the follwing statment from my testuser
>
> grant select on permission_test to dbo
>
> after this i am getting follwing errors "Invalid object name
> 'permission_test'"
>
> What should i do to avoid this problem?
>
> My doubt is that,if the owner is different from the DBO, then DBO could
> not have any acess to other owners objects?
>
> Regards
>
sql

Monday, March 26, 2012

Grant Permission issue

Grant Permission issue

Grant Permission issue

Grant Permission

Grant Permission

Grant Permission

filtering with set

Blog Archive

About Me