Recently I’ve been thinking about Python and whether it can be useful to the DBA as well as the data scientist.

One good use that I have found for it is to copy logins from one server to another.

There are plenty of times when you might want to copy your SQL logins (including the SID) from one server to another.  Perhaps you’re running an AG and need to make sure that all users exist on all your secondaries with the correct SID, perhaps you’re migrating servers and need all the logins on your new server.  Whatever the reason, there are a number of different ways in which you can do this but they usually require scripting out on one server and then running the script into another server, or of course there’s Powershell.

The below script will use Python to copy all or specified logins from one server to another, including the password and SID.

All you need to do is to pop the connection strings for the servers into @destinationConnectionString and @sourceConnectionString and Bob shall be your uncle.

Interesting Note:  Although Python can only run from a SQL2017 server, there’s no reason why it can’t connect to an earlier version.

By default the script will skip any logins that already exist on the destination server, by changing @RecreateOnSIDMismatch to 1,the script will also drop and recreate any login where the SID differs between the source and destination servers.

Another default behaviour of the script is that it will copy all logins (except for a selection of system logins, including sa), to change this you can populate the @includes and @excludes to include or exclude any specific logins.

/**********************************************
--Author: David Fowler
--Create Date: 26/10/2017
--Description: Copy logins from source server that don't exist on destination server
@RecreateSIDOnMismatch = 1, Users with a mismatching SID are dropped and recreated with the same SID as on the source server
www.sqlundercover.com
**********************************************/EXEC sp_execute_external_script
@language = N'python',
@script =
N'import pyodbc
import pandas as pa
#build where clause
if includes != "%":
WhereClause = " AND name IN (''" + includes.replace(",","'',''") + "'')"
elif excludes != "%":
WhereClause = " AND name NOT IN (''" + excludes.replace(",","'',''") + "'')"
else:
WhereClause = ""
#add WhereClause to the query
query = query + WhereClause
#get source connection
SourceConnection = pyodbc.connect(sourceConnectionString)
SourceLogins = pa.read_sql(query,SourceConnection)
#get destination connection
DestinationConnection = pyodbc.connect(destinationConnectionString)
DestinationLogins = pa.read_sql(query,DestinationConnection)
#get all logins that exist in source but not in destination, if RecreateOnSIDMismatch =1, also get logins where the SIDs are different
if RecreateOnSIDMismatch == 0:
MissingLogins = SourceLogins[~SourceLogins[''name''].isin(DestinationLogins[''name''])].dropna()
else:
MissingLogins = SourceLogins[~SourceLogins[''sid''].isin(DestinationLogins[''sid''])].dropna()
#get accounts where SIDs differ but exist on both servers
MismatchingLogins = DestinationLogins[DestinationLogins[''name''].isin(MissingLogins[''name''])].dropna()
#drop mismatching logins
for counter in range(len(MismatchingLogins.index)):
Statement = "DROP LOGIN [" + MismatchingLogins.values[counter][0] + "]"
DropLoginCursor = DestinationConnection.cursor()
DropLoginCursor.execute(Statement)
DropLoginCursor.commit()
counter = 0
#loop through and create logins on the destination server
for counter in range(len(MissingLogins.index)):
if MissingLogins.values[counter][0].find("\\") >= 0:
Statement = "CREATE LOGIN [" + MissingLogins.values[counter][0] + "] FROM WINDOWS"
else:
Statement = "CREATE LOGIN [" + MissingLogins.values[counter][0] + "] WITH PASSWORD = 0x" + MissingLogins.values[counter][2].hex() + '' HASHED, SID = 0x'' + MissingLogins.values[counter][1].hex()
NewLoginCursor = DestinationConnection.cursor()
print(Statement)
NewLoginCursor.execute(Statement)
NewLoginCursor.commit()
OutputDataSet = MissingLogins
',
@params = N'@query VARCHAR(MAX),@includes VARCHAR(4000), @excludes VARCHAR(4000), @destinationConnectionString VARCHAR(4000), @sourceConnectionString VARCHAR(4000), @RecreateOnSIDMismatch BIT',
@includes = '%',
@excludes = '%',
@destinationConnectionString = 'DRIVER={SQL Server};SERVER=laptop-fowlerd\sql2017;UID=PythonUser;PWD=P4ssw0rd',
@sourceConnectionString = 'DRIVER={SQL Server};SERVER=laptop-fowlerd\sql2016;UID=PythonUser;PWD=P4ssw0rd',
@RecreateOnSIDMismatch = 0,
@query = 'select name, CAST(sid AS VARBINARY(256)) AS sid, ISNULL(CAST(LOGINPROPERTY(name,''PasswordHash'') AS VARBINARY(256)),0x0) AS PasswordHash FROM sys.syslogins WHERE name NOT LIKE ''NT AUTHORITY%'' AND name NOT LIKE ''NT SERVICE%'' AND name NOT LIKE ''sa'' AND name NOT LIKE ''#%##'''
WITH RESULT SETS UNDEFINED