0

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?

1

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.