Script server level user securables onto another server

Question

I have restored a backup of the live DB on a test SQL Server VM.

Then I scripted the server level SQL users and restored them on the test SQL Server and fixed the orphaned user situation.

But the user securables were not scripted - is there a way to do this?

Answer 1

User securables are more relevant at user level hence, you need to check them for each database one by one. Either you can find them for one specific user within a database or for all the users in that database.

I check them using below script for one specified user:

select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where USER_NAME(p.grantee_principal_id) = 'db_user'

If you want to check for all the users in the database, just remove the last where clause as below:

select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id

You need to check class_desc column having value OBJECT_OR_COLUMN

I am not very sure however you may use the same script for server level securables being in master database however that should already be taken care by login transfer using sp_help_revlogin

For more details on securables, you may check this answer.

Hope this helps.