Wednesday, 17 October 2012

Exec Store Proc user defined Role


Several of my client request for a role which has execute permission to all store procedures in a database, most of the time I have seen DBA's giving GRANT EXECUTE permission to each and every store procedure which is also correct except for the fact that if any new store procedures get created after that we would need to remember to explicitly grant the Execute permission again. There is a much better way in which you can handle this in SQL Server as mentioned below,

--create a new role for EXEC SP
CREATE ROLE db_storeproc_executor
--grant Execute permission to the Role
GRANT EXECUTE TO db_storeproc_executor
--Assign the Role to the DB user
EXEC sp_addrolemember 'db_storeproc_executor', 'username'

By granting just EXECUTE permission to a role we make sure exec permissions are granted to any new Store Proc created.

No comments:

Post a comment