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_DBName_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_DBName_Edit (depends on level of permissions)

DBName is the actual name of the database or a short name for it if it was an oddly named one.

This is a very granular level of security but we needed to do it for various reasons.

Each application has it's own login that is put into the AD group.
This lets the DBA not have to deal with most permissions issues after the original AD groups have been set up. 
Application A has a login it runs under and that is added to the PRD_DBName_Read/Edit AD group.
Users can be added to the PRD_DBName_Read/Edit AD group by an admin.
When a user leaves, they are automatically removed so the DBA doesn't have to manage them anymore.

Now for the script itself.:

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

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

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

SELECT
@CreateUser = @CreateUser + 'USE [master]; CREATE LOGIN [' + 
REPLACE(CONVERT(VARCHAR(500),[sp].[name]),'PRD_', 'TST_') +
'] FROM WINDOWS
;'
FROM
[sys].[server_principals] AS [sp] WITH (NOLOCK)
WHERE
[sp].[name] LIKE 'domain\PRD_%'
AND [sp].[name] NOT LIKE 'domain\TST_%'

INSERT INTO [#Logins] SELECT @CreateUser

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

OPEN DBNames_Cursor
FETCH NEXT FROM DBNames_Cursor INTO @DBName

WHILE (@@FETCH_STATUS <> -1)
BEGIN /* @@FETCH_STATUS <> -1 */
IF (@@FETCH_STATUS <> -2)
BEGIN /* @@FETCH_STATUS <> -2 */
--
SELECT @sqltext = @sqltext + '
SELECT ''USE [' + @DBName +']; 
CREATE USER ['' + REPLACE(CONVERT(VARCHAR(500),[dbp].[name]),''PRD_'', ''TST_'') 
+ ''] FOR LOGIN [''
+ REPLACE(CONVERT(VARCHAR(500),[dbp].[name]),''PRD_'', ''TST_'') 
+ ''];''
FROM
[' + @DBName + '].[sys].[database_principals] AS [dbp] WITH (NOLOCK)
WHERE
[dbp].[name] LIKE ''domain\PRD_%''
AND [dbp].[name] NOT LIKE ''domain\TST_%'''

BEGIN TRY
PRINT @sqltext
INSERT INTO #Logins
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 @sqltext = ''

END /* @@FETCH_STATUS <> -2 */
FETCH NEXT FROM DBNames_Cursor INTO @DBName
END /* @@FETCH_STATUS <> -1 */
CLOSE DBNames_Cursor
DEALLOCATE DBNames_Cursor

SELECT * FROM #Logins;


#MicrosoftSQL
#Logins
#DynamicSQL
#BuckTheSQLDude

Comments

Post a Comment

Popular posts from this blog

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 ...
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