Technical Article

Script All Object Permissions

,

Quick and dirty script to generate the required SQL to reinstate permissions on each user object.  This can be saved off for DR purposes.

SELECT "GRANT "+
CASE c.action 
WHEN 26 THEN 'REFERENCES'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
ELSE 'CREATE RULE'
END+" ON "+a.name+" TO "+b.name+char(13)+"GO"
FROM sysobjects a 
INNER JOIN sysprotects c
ON a.id = c.id
INNER JOIN sysusers b
ON c.uid = b.uid
WHERE a.type IN ('U','P','V')
ORDER BY a.name

Rate

Share

Share

Rate