Recreating Permissions from 1 server to another for a different group

The second script I am going to post is used to copy all the permissions in all databases for a login matching the pattern and generating the GRANT permissions for a different login.
You could swap out the REPLACE line with just the [dbprinc].[name] if you want the same group to have them assigned on a different server.

I used this to migrate permissions to both DEV and TEST for 3 servers.
All told, it generated about 19,000 rows between them.
Imagine having to copy those manually. 


/***********************************************/
/*************       START CODE       ***************/
/***********************************************/

DECLARE
@DBName VARCHAR (500)
, @sqltext NVARCHAR(MAX)

SELECT @DBName = ''
SELECT @sqltext = ''

IF OBJECT_ID('[tempdb]..#Perms') IS NOT NULL
BEGIN
DROP TABLE #Perms
END 
CREATE TABLE #Perms
(
[PermString] VARCHAR(MAX)
)

DECLARE DBNames_Cursor CURSOR STATIC FOR 
SELECT -- * ,
[s].[name]
FROM 
master.dbo.[sysdatabases] AS [s]    WITH (NOLOCK)
WHERE 
[s].[name] <> 'master'
AND [s].
ORDER BY 
[s].[name]

OPEN DBNames_Cursor
FETCH NEXT FROM DBNames_Cursor INTO @DBName

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- SELECT @DBName AS [DBName]

-- drop user from all DBs
SELECT @sqltext = 'USE [' + @DBName +']
-- SELECT DB_NAME()

; 
'
SELECT @sqltext =  @sqltext + '
SELECT ''USE [' + @DBName +']; GRANT '' + [dbperm].[permission_name] 
+ '' ON ['' + [sch].[name] COLLATE DATABASE_DEFAULT 
+  ''].['' + [so].[name] COLLATE DATABASE_DEFAULT  + ''] TO ['' 
+ REPLACE(CONVERT(VARCHAR(500),[dbprinc].[name]),''PRD_'', ''DEV_'') 

+ ''];''

FROM
[' + @DBName +'].[sys].[database_principals] AS [dbprinc] WITH (NOLOCK)
INNER JOIN
[' + @DBName +'].[sys].[database_permissions] AS [dbperm] WITH (NOLOCK) ON
[dbperm].[grantee_principal_id] = [dbprinc].[principal_id]
INNER JOIN
[' + @DBName +'].[sys].[objects] AS [so] WITH (NOLOCK) ON
[so].[object_id] = [dbperm].[major_id]
INNER JOIN
[' + @DBName +'].[sys].[schemas] AS [sch] WITH (NOLOCK) ON
[sch].[schema_id] = [so].[schema_id]

WHERE
[dbprinc].[name] NOT IN (''public'',''guest'')
AND [dbprinc].[name] LIKE ''domain\PRD_%''


ORDER BY
[so].[type]
, [so].[name]
, [dbprinc].[name]
, [dbperm].[permission_name]
'

-- PUT IN TRY CATCH TO RUN SQL
BEGIN TRY
---- All PRINT Must be done before the EXECUTE so error handler manages the EXECUTE
PRINT @sqltext
INSERT INTO #Perms EXECUTE [sys].[sp_executesql] @sqltext
END TRY
BEGIN CATCH
SELECT  
ERROR_NUMBER() AS ErrorNumber  
,ERROR_SEVERITY() AS ErrorSeverity  
,ERROR_STATE() AS ErrorState  
,ERROR_PROCEDURE() AS ErrorProcedure  
,ERROR_LINE() AS ErrorLine  
,ERROR_MESSAGE() AS ErrorMessage  
, @sqltext
END CATCH
 
SELECT @DBName = ''
SELECT @sqltext = ''


END
FETCH NEXT FROM DBNames_Cursor INTO @DBName
END
CLOSE DBNames_Cursor
DEALLOCATE DBNames_Cursor

SELECT * FROM [#Perms] AS [p] WITH (NOLOCK)

/***********************************************/
/*************        END CODE         ***************/
/***********************************************/

This will create the following SQL that you paste into a new query window to run it.
USE [ApplicationLogging]; GRANT SELECT ON [dbo].[ApplicationEnvironment] TO [domain\DEV_Application_Read];



#MicrosoftSQL
#Logins
#DynamicSQL
#BuckTheSQLDude

Comments

Post a Comment

Popular posts from this blog

Recreating Logins From 1 Server to Apply to Another

A little background on this script. We had to build out a new TEST environment along with a new security schema. We had to apply it to PROD first due to issues and then migrate it down. With nearly 500 DBs on multiple servers, I didn't want to have to try to redo all of the permissions to each object manually. I build this script to cycle thru all of the logins on the server that fit a particular AD pattern and generate the CREATE script for them with the TEST name instead.  Once the script was run, all I had to do was copy it, paste it in the TEST environment, and run it.  It automatically adds the logins to each DB not just to the server itself. Each environment has an AD group for the DB in a standard pattern that is specific to that environment. The pattern is something like this: PROD - PRD_ DBName _Read or PRD_ DBNam e_Edit (depends on level of permissions) TEST - TST_ DBName _Read or TST_ DBName _Edit (depends on level of permissions) DEV - DEV_ DBName _Read or DEV_ DBN...
Read more

SP to Cycle thru All Linked Servers

We have a central DB server for the DBAs to query other SQL servers from. It has linked servers to the various servers that allow us to centralize some processes. Today, I am going to post the basic template I use for cycling thru those servers. This template happens to pull back all of the SQL Job Owners for the various SQL jobs. We do this to prevent PROD jobs from running under a users login. If the user leaves, the jobs will fail and that has caused issues before. Obviously you can change out that the dynamic SQL does but this works well as a basic template to query against all of the other servers to get data and load it back into a single central table to manage the data.  /****** Object:  StoredProcedure [security].[templateCycleAllLinkedServers] ******/ -- ============================================= -- Author: -- Create date: 20210526 -- Description: Template for process to cycle thru all the servers to do something -- Calling App: -- Ticket Info: -- ...
Read more