Tag Archives: sys.database_principals

Scripting User Database Rights

All DBA’s will be tasked at time to refresh a copy of production to a development, reporting or promote as adatbase to a UAT or QA environment. Many times it is important to retain the existing users rights on the target instance.
The script uses the sys.database_role_members, sys.database_principals, sys.database_permissions, sys.sysobjects and sys.sysusers tables.

Some things to note:

  • Script does not remove or reduce rights if the user already exists.
  • Create user statements that test if users already exits.
  • Add users to existing database roles.
  • Scripts object level grants.

Read more »