Sql-server – SQL Server 2008 R2 copy Schema with all user security permissions

permissionsschemasql-server-2008-r2ssmsssrs-2008-r2

I have a prod support request from a high value user to adjust the settings on a company-wide dashboard so that he can manage a section of his workflow currently being excluded from these reports. Given the two immediately obvious options of

  1. altering the dashboard settings for ALL users company-wide or
  2. replicating the dashboard for HVU only

…it was decided that I should create a "dev" instance where HVU can see a variant of his dashboard. To accomplish this, I ported the .rdl family to another ReportServer instance in an identical directory tree & cloned all the stored procedures in the pertinent datasets from Schema1 to Schema1_dev.

Question: the stored procedures & security schema all exist on the same server & database so there's no risk of orphan users. Is it possible / what's the best way to clone all current user permissions from Schema1 to Schema1_dev other than checking through the SSMS GUI?

Best Answer

This should give you a start. It assumes that you've already cloned all the relevant objects and that all of your permissions are at the object or schema level (if you have column-level permissions, the script gets a little more complex, but not too bad).

DECLARE 
  @oldschema SYSNAME = N'schema1', 
  @newschema SYSNAME = N'schema1_dev';

DECLARE 
  @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
 ' + p.state_desc + N' ' 
 + p.[permission_name] + N' ON '
 + QUOTENAME(@newschema) + N'.' + QUOTENAME(o.name)
 + N' TO ' + QUOTENAME(dp.name) + N';'
FROM sys.database_permissions AS p
INNER JOIN sys.objects AS o
ON p.major_id = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
WHERE p.class_desc = N'OBJECT_OR_COLUMN'
AND s.name = @oldschema;

SELECT @sql += N'
  ' + p.state_desc + N' '
  + p.[permission_name] + N' ON SCHEMA::' 
  + QUOTENAME(@newschema) 
  + N' TO ' + QUOTENAME(dp.name) + N';'
FROM sys.database_permissions AS p
INNER JOIN sys.schemas AS s
ON p.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
WHERE p.class_desc = N'SCHEMA' 
AND s.name = @oldschema;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Note that if you have a lot of objects and/or a lot of users, the PRINT output might not show everything.

Related Question