1

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?

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.