Script out all permissions (select/execute etc) from a sql server database

SELECT ‘GRANT ‘ + dp.permission_name collate latin1_general_cs_as
+ ‘ ON ‘ + s.name + ‘.[‘ + o.name + ‘] TO ‘ + dpr.name
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN (‘public’,’guest’)
— AND permission_name=’EXECUTE’

Source: http://stackoverflow.com/questions/1987190/scripting-sql-server-permissions

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: