I found it a chore to create execute permissions on all stored procedures in a database.
This query will do the job for you:
CREATE A ROLE AS FOLLOWS:
1.
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
2.
AND THEN when creating user, assign db_executor as role rather than dbo.
3.To assign user to all stored procedures in database run this.
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
Wednesday 21 November 2007
Granting Execute Permissions to a user in SQL2005
Posted by Omen at 10:17
Labels: SQL Server 2005
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment