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
-Spsk
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
|||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...
>
>
|||Not that I am aware of.
"Spsk" <Spsk@.discussions.microsoft.com> wrote in message
news:1B9A9367-8643-4014-9E22-BC40415DC5CC@.microsoft.com...[vbcol=seagreen]
> 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:
|||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...
>
sql