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
- Create the procedure in the source database, then run it from that database:
USE AppDb;
GO
EXEC dbo.usp_get_object_permissions;
- 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.
- 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
- The matching logins must exist on the target server, ideally with the same SID to avoid orphaned users; server roles must be handled separately.
- The password of application roles cannot be retrieved: the script contains a placeholder to replace (
<PASSWORD>). sp_addrolememberis deprecated; on recent versions you can replace it withALTER ROLE ... ADD MEMBER ....