Tuesday, August 5, 2008

Granting view definitation only permissions to stored procedures

First to just give execute permissions on Stored Procedure
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
/* ADDING USERS TO ROLE*/
EXEC sp_addrolemember N'db_executor', N'AppUser'

Once you have done above you should not need to to following for every new object SP addded
select 'Grant Execute on ' + name + ' to [insertUserNameHere]'
from sysobjects where xtype in ('P')

'To give View permission on the stored Procs and Objects like this'
select 'Grant VIEW DEFINITION on ' + name + ' to db_executor'
from sysobjects where xtype in ('P')

No comments: