T-SQL Script current user database permissions

Question

Occasionally I have to restore DB's from our production SQL server to the test SQL instance. When the database has been restored, we manually restore the correct access permissions (e.g. DB owner/ reader/ writer) for the restored database. This process works fairly well, except for having to manually screenshot the permissions before the restore and then reapply them from the image taken.

Is there an easy way to use T-SQL to store CURRENT permissions for a user BEFORE the database restore and then reapply those same permissions once the restoration is complete?

Answer 1

The answer to your question is most probably the one from Jeff.

But the script from Howard is very practical, i just added a column, which generates the TSQL Syntax with the information. You can copy that and run as SQL to 'replicate' permissions to another db.

SELECT 
dp.permission_name collate latin1_general_cs_as    AS Permission,
t.TABLE_SCHEMA + '.' + o.name AS Object,
dpr.name AS Username
, 'GRANT ' + dp.permission_name collate latin1_general_cs_as 
    + ' ON ' 
    + t.TABLE_SCHEMA 
    + '.' 
    + 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
INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON  TABLE_NAME = o.name                
WHERE dpr.name NOT IN ('public','guest')
ORDER BY
   Permission, Object,Username