CREATE
-- =========================================
-- Script: Extract Database Security Objects
-- Purpose: Generate SQL to recreate users, roles, memberships, and permissions
-- Works for: Current database
-- =========================================
DECLARE
@DBName sysname,
@sql_perms nvarchar(max);
SET @DBName = DB_NAME();
SET @sql_perms = N'
---------------------------
-- Database Users
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '''''' + dp.name + '''''')
CREATE USER ['' + dp.name + '']'') COLLATE DATABASE_DEFAULT AS SQLTEXT
FROM sys.database_principals dp
WHERE dp.principal_id > 4
AND dp.type IN (''S'', ''U'', ''G'')
AND sid IS NOT NULL
AND sid IN (SELECT sid FROM master..syslogins)
UNION ALL
---------------------------
-- Database Roles
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '''''' + dp.name + '''''' AND type = ''''R'''')
CREATE ROLE ['' + dp.name + '']'') COLLATE DATABASE_DEFAULT AS SQLTEXT
FROM sys.database_principals dp
WHERE dp.type IN (''R'', ''A'')
AND dp.name <> ''public''
AND dp.is_fixed_role <> 1
UNION ALL
---------------------------
-- Role Memberships
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF NOT EXISTS (
SELECT 1 FROM sys.database_role_members
WHERE user_name(role_principal_id) = '''''' + user_name(DRM.role_principal_id) + ''''''
AND user_name(member_principal_id) = '''''' + user_name(DRM.member_principal_id) + '''''')
ALTER ROLE ['' + USER_NAME(DRM.role_principal_id) + '']
ADD MEMBER ['' + USER_NAME(DRM.member_principal_id) + '']'') COLLATE DATABASE_DEFAULT AS SQLTEXT
FROM sys.database_role_members DRM
INNER JOIN sys.database_principals DP
ON DRM.member_principal_id = DP.principal_id
WHERE DRM.member_principal_id > 1
AND DP.sid IS NOT NULL AND DP.sid IN (SELECT sid FROM master..syslogins)
UNION ALL
---------------------------
-- Object Permissions
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = '''''' + OBJECT_NAME(major_id) + '''''' INTERSECT SELECT 1 FROM sys.database_principals WHERE name = '''''' + USER_NAME(DP.grantee_principal_id) + '''''')
'' + DP.state_desc + '' '' + DP.permission_name + '' ON ['' +
SCHEMA_NAME(SO.schema_id) + ''].['' + OBJECT_NAME(DP.major_id) + '']
TO ['' + USER_NAME(DP.grantee_principal_id) + '']'') COLLATE DATABASE_DEFAULT AS SQLTEXT
FROM sys.database_permissions DP
INNER JOIN sys.database_principals DPS
ON DP.grantee_principal_id = DPS.principal_id
INNER JOIN sys.objects SO
ON SO.object_id = DP.major_id
WHERE DPS.name NOT IN (''public'', ''guest'')
AND (
-- Include ALL database roles
DPS.type_desc IN (''DATABASE_ROLE'', ''APPLICATION_ROLE'')
OR
-- Include only valid (non-orphaned) SQL users and Windows users/groups
(DPS.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'')
AND DPS.sid IS NOT NULL
AND EXISTS (SELECT 1 FROM master.sys.server_principals SP
WHERE SP.sid = DPS.sid))
);
';
PRINT 'Executing in database: ' + @DBName;
EXEC sp_executesql @sql_perms;
DROP
-- =========================================
-- Script: Extract Database Security Objects to DROP
-- Purpose: Generate SQL to drop users, roles, memberships, and permissions
-- Works for: Current database
-- =========================================
DECLARE
@DBName sysname,
@sql_perms nvarchar(max);
SET @DBName = DB_NAME();
SET @sql_perms = N'
---------------------------
-- Object Permissions
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = '''''' + OBJECT_NAME(major_id) + '''''' INTERSECT SELECT 1 FROM sys.database_principals WHERE name = '''''' + USER_NAME(DP.grantee_principal_id) + '''''')
REVOKE '' + permission_name + '' ON ['' + SCHEMA_NAME(SO.schema_id) + ''].[''+OBJECT_NAME(DP.major_id) +
''] FROM ['' + USER_NAME(DP.grantee_principal_id) + '']'') COLLATE DATABASE_DEFAULT as SQLTEXT
from sys.database_permissions DP
INNER JOIN sys.database_principals DPS
ON DP.grantee_principal_id=DPS.principal_id
Inner Join sys.objects SO ON SO.object_id=DP.major_id
where DPS.name not in (''public'',''Guest'')
and DPS.type != ''R''
UNION ALL
---------------------------
-- Role Memberships
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF EXISTS (
SELECT 1 FROM sys.database_role_members
WHERE user_name(role_principal_id) = '''''' + user_name(DRM.role_principal_id) + ''''''
AND user_name(member_principal_id) = '''''' + user_name(DRM.member_principal_id) + ''''''
)
ALTER ROLE [''+ user_name(DRM.role_principal_id)+ '']
DROP MEMBER ['' + user_name(DRM.member_principal_id) + '']'') COLLATE DATABASE_DEFAULT AS SQLTEXT
from sys.database_role_members DRM
inner join sys.database_principals DP
on DRM.member_principal_id=DP.principal_id
where DRM.member_principal_id>1
UNION ALL
---------------------------
-- ORPHAN SCHEMAS
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF EXISTS (
SELECT 1 FROM sys.schemas WHERE name = '''''' + name + '''''')
DROP SCHEMA [''+name+'']'') as SQLTEXT --[Command to Drop Orphaned Schema]
from sys.schemas
where schema_id not in (select o.schema_id from sys.objects o, sys.schemas s where s.schema_id = o.schema_id and o.schema_id between 5 and 16383)
and schema_id between 5 and 16383
and principal_id between 5 and 16383
UNION ALL
---------------------------
-- Database Users
---------------------------
SELECT
(''USE ' + QUOTENAME(@DBName) + N';
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '''''' + name + '''''')
DROP USER [''+name+'']'') COLLATE DATABASE_DEFAULT AS SQLTEXT
from sys.database_principals
where principal_id > 4 and type in (''S'', ''U'' , ''G'')
and name not in (
select user_name(principal_id) from sys.schemas
where schema_id in (select o.schema_id from sys.objects o, sys.schemas s where s.schema_id = o.schema_id and o.schema_id between 5 and 16383)
and schema_id between 5 and 16383
and principal_id between 5 and 16383)
';
PRINT 'Executing in database: ' + @DBName;
EXEC sp_executesql @sql_perms;