LLI Wiki — My Digital Knowledge Base

SQL Server: script the users, roles and permissions of a database

Copy a database's security to another by generating a T-SQL script with the usp_get_object_permissions procedure.

To copy the security of one SQL Server database to another (for example after restoring it on another server), you can generate a T-SQL script that recreates the users, roles and permissions of the source database. A well-known procedure for this is usp_get_object_permissions, published on SQLServerCentral: Reverse engineer database permissions (2005 up). The code is not reproduced here; get it from the source page and check its licence.

Usage

  1. Create the procedure in the source database, then run it from that database:
USE AppDb;
GO
EXEC dbo.usp_get_object_permissions;
  1. The procedure changes nothing: it returns several result sets in which each row is a T-SQL statement. Copy those rows, review them, then run them in the target database.
  2. Drop the procedure when you no longer need it (DROP PROCEDURE dbo.usp_get_object_permissions).

What the script generates, in order

Result set Content
Owner ALTER AUTHORIZATION ON DATABASE::... to restore the database owner
Drop users DROP USER for users (system accounts excluded); not always needed
Create users CREATE USER ... FOR LOGIN ... with the default schema; orphaned users are not recreated
Database roles CREATE ROLE for user-defined roles
Application roles CREATE APPLICATION ROLE with a password to fill in
Role members EXEC sp_addrolemember for each member
Object and column permissions GRANT / DENY (with WITH GRANT OPTION when needed)
Schema permissions GRANT / DENY on SCHEMA::...
VIEW DEFINITION Database-level permissions

Notes

Mastodon LLIMastodon SocialFosstodon