DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to grant execute permission on all sprocs?

Execute the following script generator Microsoft SQL Server T-SQL query in SSMS Query Editor after setting the results to TEXT mode. Paste the resulting script back to the query window for deployment execution:

 

USE pubs;

 

DECLARE @DBrole sysname

SET @DBrole = 'RoleDELTA'

 

-- SQL execute permission check

PRINT 'Not permissioned'

SELECT name FROM sysobjects

WHERE xtype='P' AND base_schema_ver < 16

AND id NOT IN

            ( SELECT o.id FROM syspermissions p

                  LEFT JOIN sysusers u ON p.grantee = u.uid

                  LEFT JOIN sysobjects o ON p.id = o.id

                  WHERE lower(u.name) = lower(@DBrole)

            )

 

-- SQL grant execute - generate statements

PRINT 'GRANT EXECUTE generation '

SELECT 'GRANT EXECUTE ON [' + name + '] TO [' + @DBrole + ']'

FROM sysobjects

WHERE xtype='P' AND base_schema_ver <> 16

      AND id NOT IN

            ( SELECT o.id FROM syspermissions p

                  LEFT JOIN sysusers u ON p.grantee = u.uid

                  LEFT JOIN sysobjects o ON p.id = o.id

                  WHERE lower(u.name) = lower(@DBrole)

            )

GO

 

/* Partial results

 

GRANT EXECUTE ON [byroyalty] TO [RoleDELTA]

GRANT EXECUTE ON [CustomerListByState] TO [RoleDELTA]

*/

 

 

Related article:

 

http://www.sqlusa.com/bestpractices/grantexectorole/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE