Looking for a script which will script out all database level persmissions( including xp_cmdshell and sql proxy account)

Question

I need a stored procedure or script which will script out all database level permissions- including permissions to xp_cmdshell for any objects as well as permissions to a sql proxy account.

Thanks!

Answer 1

Your problem is going to be that xp_cmdshell is a master database permission, so unless you are traversing databases on the server, you won't be able to do what you are wanting to do. If you are trying to grant all the permissions for a database to a proxy account, you should just put that account in the db_owner database role, and it gets those rights through the role membership. Xp_cmdshell is a separate explicit grant in master, and would have to be enabled through sp_configure, and should be used for legacy integration, it shouldn't be used for new development work. At the very least, if you use xp_cmdshell a proxy account should be setup following Tibor's example here:

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

The permissions for a specific user could be scripted by querying the DMV's similar to the following:

SELECT
permission.state_desc,
permission.permission_name,
obj.name
FROM
sys.all_objects AS obj
INNER JOIN sys.database_permissions AS permission ON permission.major_id=obj.object_id AND permission.minor_id=0 AND permission.class=1
INNER JOIN sys.database_principals AS grantor_principal ON grantor_principal.principal_id = permission.grantor_principal_id
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = permission.grantee_principal_id
WHERE (grantee_principal.name=N'JohnDoe')

Answer 2

I found another script as well:

SELECT dp.name db_principal_name, p.permission_name, COALESCE(o.type_desc,p.class_desc) + CASE WHEN o.type_desc IS NOT NULL AND minor_id > 0 THEN '-COLUMN' ELSE '' END AS object_type, CASE p.class_desc WHEN 'SCHEMA' THEN schema_name(major_id) WHEN 'OBJECT_OR_COLUMN' THEN CASE WHEN minor_id = 0 THEN object_name(major_id) ELSE (SELECT object_name(object_id) + '.'+ name FROM sys.columns WHERE object_id = p.major_id and column_id = p.minor_id) END ELSE 'other' END AS object_name, p.state_desc AS grant_state, CONVERT(VARCHAR(MAX),p.state_desc) + ' ' + CONVERT(VARCHAR(MAX),p.permission_name) + ' ON ' + CASE WHEN minor_id = 0 THEN object_name(major_id) ELSE (SELECT object_name(object_id) + '.'+ name FROM sys.columns WHERE object_id = p.major_id and column_id = p.minor_id) END+ ' TO [' + CONVERT(VARCHAR(MAX),dp.NAME) + ']' Collate SQL_Latin1_General_CP1_CI_AS

FROM sys.database_permissions p INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id LEFT OUTER JOIN sys.objects o ON o.object_id = p.major_id --WHERE dp.name <> 'public' ORDER BY dp.name, object_name