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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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