DROP
— Script to extract and DROP Database Security Objects from a refreshed database before putting in place the correct permissions
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 dp.major_id = SO.object_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
— 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;
CREATE
— Script to extract Database Security Objects for current database before a refresh
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”)
UNION ALL
— 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 role_principal_id = [” + USER_NAME(DRM.role_principal_id) + ”]
and 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
UNION ALL
— Object Permissions
select
(”USE ‘ + QUOTENAME(@DBName) + N’;
” + dp.state_desc + ” ” + dp.permission_name + ” ON [” +
SCHEMA_NAME(o.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 grantee
ON dp.grantee_principal_id = grantee.principal_id
INNER JOIN sys.objects o
ON dp.major_id = o.object_id
WHERE grantee.name NOT IN (”public”, ”guest”);
‘;
PRINT ‘Executing in database: ‘ + @DBName;
EXEC sp_executesql @sql_perms;