Logins, SIDs, and Kerberos from First Principles, Part 4: Scripting Every Permission a Principal Holds
Part 3 moved logins between servers with their SIDs and passwords intact. That gets a principal through the front door; it says nothing about what the principal can do once inside. Permissions live in a different set of catalog views, at multiple scopes, and they do not travel inside a CREATE LOGIN statement. Database-scoped permissions do travel inside database backups, but server-scoped permissions, and any permissions you want to compare, audit, or re-apply selectively, have to be scripted.
This post builds the permission scripting queries from the catalog up, using a demo principal with a deliberately messy mix of grants. Demos ran against SQL Server 2019 (CU32); the catalog views are identical back to 2012 and forward through 2025.
Where Permissions Actually Live
Three catalog families hold everything:
| Scope | Role memberships | Explicit permissions |
|---|---|---|
| Server | sys.server_role_members |
sys.server_permissions |
| Database | sys.database_role_members |
sys.database_permissions |
Explicit permissions are the interesting ones, because each row carries a class that says what the permission applies to: the whole database (class 0), a specific object (class 1), a schema (class 3), and a dozen rarer classes (certificates, assemblies, full-text catalogs).[1] A scripting query has to decode the class to reconstruct the ON clause.
A Messy Test Subject
The fleet_app user gets a realistic tangle: a fixed role, object-level grants, a schema-level grant, a database-scoped grant, and, importantly, a DENY:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE [fleet_demo]; CREATE USER [fleet_app] FOR LOGIN [fleet_app]; GRANT SELECT, INSERT ON [dbo].[vehicles] TO [fleet_app]; DENY DELETE ON [dbo].[vehicles] TO [fleet_app]; GRANT SELECT ON SCHEMA::[reporting] TO [fleet_app]; GRANT SHOWPLAN TO [fleet_app]; ALTER ROLE [db_datareader] ADD MEMBER [fleet_app]; |
Scripting Role Memberships
The simpler half. Self-join sys.database_principals through the role-membership table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT N'ALTER ROLE ' + QUOTENAME([r].[name]) + N' ADD MEMBER ' + QUOTENAME([m].[name]) + N';' AS [cmd] FROM [sys].[database_role_members] AS [drm] INNER JOIN [sys].[database_principals] AS [r] ON [drm].[role_principal_id] = [r].[principal_id] INNER JOIN [sys].[database_principals] AS [m] ON [drm].[member_principal_id] = [m].[principal_id] WHERE [m].[name] = N'fleet_app'; |
|
1 |
ALTER ROLE [db_datareader] ADD MEMBER [fleet_app]; |
The server-level version is structurally identical against sys.server_role_members and sys.server_principals.
Scripting Explicit Permissions
The general shape of a permission statement is {GRANT | DENY} {permission} [ON {securable}] TO {principal}, and every piece comes from sys.database_permissions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT [dp].[state_desc] COLLATE DATABASE_DEFAULT + N' ' + [dp].[permission_name] COLLATE DATABASE_DEFAULT + CASE [dp].[class] WHEN 0 THEN N'' /* database scope: no ON clause */ WHEN 1 THEN N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME([dp].[major_id])) + N'.' + QUOTENAME(OBJECT_NAME([dp].[major_id])) WHEN 3 THEN N' ON SCHEMA::' + QUOTENAME(SCHEMA_NAME([dp].[major_id])) ELSE N' /* class ' + CONVERT(nvarchar(10), [dp].[class]) + N': extend for this class */' END + N' TO ' + QUOTENAME([pr].[name]) + N';' AS [cmd] FROM [sys].[database_permissions] AS [dp] INNER JOIN [sys].[database_principals] AS [pr] ON [dp].[grantee_principal_id] = [pr].[principal_id] WHERE [pr].[name] = N'fleet_app' ORDER BY [dp].[class] , [dp].[permission_name]; |
|
1 2 3 4 5 6 |
GRANT CONNECT TO [fleet_app]; GRANT SHOWPLAN TO [fleet_app]; DENY DELETE ON [dbo].[vehicles] TO [fleet_app]; GRANT INSERT ON [dbo].[vehicles] TO [fleet_app]; GRANT SELECT ON [dbo].[vehicles] TO [fleet_app]; GRANT SELECT ON SCHEMA::[reporting] TO [fleet_app]; |
Every grant we made is reconstructed, plus one we did not: GRANT CONNECT, which CREATE USER issued implicitly. The DENY is preserved too, and that matters: a migration script that only replays GRANTs silently drops DENYs, converting an explicit prohibition into an implicit permission-by-role.[3] If a DENY existed, someone put it there for a reason.
Two Gotchas From the Field
Collation conflicts. The COLLATE DATABASE_DEFAULT clauses on state_desc and permission_name are not decoration. Catalog view metadata columns use the instance collation, while OBJECT_NAME() and friends return the database collation. On a server where the two differ, concatenating them raises:
|
1 2 3 |
Msg 451, Level 16, State 1 Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in add operator occurring in SELECT statement column 1. |
The demo instance for this post has exactly that mismatch, so the error above is genuine. Scripting queries meant to run on arbitrary servers should collate every string column they concatenate.
Version-gated securable classes. New SQL Server versions add new permission classes (external languages arrived in 2019, for example). A generator written for one version silently skips, or errors on, classes it does not decode. The ELSE branch above makes unknown classes visible in the output instead of dropping them; a permission you did not script is a permission that quietly disappears on the target.
Assembling the Migration Kit
Combined with part 3, the full server migration sequence is:
- Generate portable
CREATE LOGINstatements (SIDs + password hashes) on the source. - Generate server role memberships and server-scoped permissions (
sys.server_permissions,[2] same pattern as above withGRANT ... TOand noUSE). - Replay both on the target before restoring databases.
- Restore databases; users map by SID automatically, and their database-scoped permissions arrive inside the backup.
- Run the database-permission script on both sides and diff the outputs as a verification step, not a repair step.
One caution about generators in general: any script that builds T-SQL strings from catalog data and executes them is a code-generation tool, and principal names can contain characters that break naive concatenation. QUOTENAME() on every identifier is the minimum bar; treat generated scripts as something to review, not something to pipe blindly into production.
What to Take Away
- Permissions live at multiple scopes, and only database-scoped ones travel inside backups. Server-scoped permissions must be scripted or they are lost.
- Decode the permission class to rebuild the
ONclause; make unknown classes loud, not silent. - Preserve DENYs. A GRANT-only migration is a security regression with a delay timer.
- Collate everything you concatenate in scripts meant for arbitrary servers.
Next up: part 5 tackles Windows groups, where the principal logging in does not appear in sys.server_principals at all.
Got a permission-migration horror story? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.
References
- sys.database_permissions (Transact-SQL) – Microsoft Learn. Permission classes, states, and major_id resolution. ↩
- sys.server_permissions (Transact-SQL) – Microsoft Learn. The server-scoped counterpart. ↩
- DENY (Transact-SQL) – Microsoft Learn. DENY precedence over GRANT, including via role membership. ↩