0

I'm trying to figure out how to backup the users, roles, and what permissions they have on a SQL Server instance. I need to be able to restore the users, roles, and permissions without changing anything else (so backing up the entire database is out). I'm not trying to figure out what permissions are required to be able to make a backup, or restore one.

The reason I'm looking into this:

I need my QA environment to retain its current users, roles, and permissions. I also need to refresh the QA instance (data and objects) based on my prod instance. Our QA team has some elevated permissions that we don't want them to lose after the QA environment is refreshed. The elevated permissions are not documented, and the person who granted them is no longer with the organization. Nobody is confident that we know what they all are.

2
  • How exactly do you "refresh the QA instance (data and objects)"? – mustaccio Sep 21 '20 at 20:23
  • Are you asking how it's done, or what I mean by that? – Mic Sep 22 '20 at 0:40
0

The elevated permissions are not documented, and the person who granted them is no longer with the organization. Nobody is confident that we know what they all are.

You'll have to fix that.

Sart with dumping the easy stuff, like the explicit permissions, role memberships, and members of the QA role. eg

--qa role explicit permissions
select *
from sys.database_permissions p
where grantee_principal_id = user_id('qa')

--roles that the qa role is added to
select r.name role_name
from sys.database_principals r
join sys.database_role_members rm
  on rm.role_principal_id = r.principal_id
where rm.member_principal_id = user_id('qa')

---members of the qa role
select user_name(rm.member_principal_id) qa_member
from sys.database_principals r
join sys.database_role_members rm
  on rm.role_principal_id = r.principal_id
where rm.role_principal_id = user_id('qa')

And use those to generate a permissions script that is applied after restore to QA. But keep a copy of your old QA database online so you can find stuff you missed. EG individal team members with directly-granted permissions, etc.

1

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.