Scripting User Level Permission SQL2005

This script gives a out script that can be used to script out the existing user permissions in a database.This is compatible only to SQL 2005.

Script

SET NOCOUNT ON
PRINT 'Column Level Privileges to the User:'
SELECT 'grant '+privilege_type+' on '+table_schema+'.'+table_name+' ('+column_name+') to ['+grantee+']'+
CASE IS_GRANTABLE WHEN 'YES' THEN ' With GRANT OPTION'
ELSE '' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
PRINT 'Table Level Privileges to the User:'
SELECT 'grant '+privilege_type+' on '+table_schema+'.'+table_name+' to ['+grantee+']' +
CASE IS_GRANTABLE WHEN 'YES' THEN ' With GRANT OPTION'
ELSE '' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
PRINT 'Privileges for Procedures/Functions to the User:'
SELECT 'grant execute on '+c.name+'.'+a.name+' to '+USER_NAME(b.grantee_principal_id)+
CASE STATE WHEN 'W' THEN ' with grant option'
ELSE '' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c
WHERE a.object_id = b.major_id AND a.type IN ('P','FN') AND b.grantee_principal_id<>0
AND b.grantee_principal_id <>2 AND a.schema_id=c.schema_id 

Leave a Reply

Leave a comment

Your email address will not be published. Required fields are marked *

*