Prior SQL 2005 there is no specific methods to grant execute permission on all stored procedures at one step. We need to give permission one by one or need to a script to do it.
SQL 2005 has provision to grant execute permission at database level. That means if we issue the below statement, it will grant execute permission on all existing stored procedures and scalar functions and for newly created ones.
Grant Execute to ‘Domain\User’
Grant Execute to ‘SQL User’
Grant Execute to ‘Windows Group’
This can be done in several ways. We can create a separate role and we can assign that role to SQL Logins / Windows users / Windows Groups
Create Role DB_ProcsExecutor
Grant Execute to DB_ProcsExecutor
Using sp_addrolemember we can assign role to any login as below
sp_addrolemember 'DB_ProcsExecutor', 'TestUser'
No comments: