How to copy the logins from one SQL Server to another SQL Server
This situation does arise when client wants to setup the production environment after the complete testing is done in QA/Test server.
Production server should replicate Test server including logins and users.
It is known fact that User exist at database level not at server level.
So, Login migration from one server to another is different from Users migration. This should be clear before proceeding.
How many types of Logins will be there in SQL Server
1. Windows Login (Windows Authenticated)
2. SQL Login (SQL Server Authenticated).
Windows Login is not created in SQL Server, infact it is just granted permission to connect to SQL Server and do what all privileges provided to the particular account.
In contrast, SQL Server Login is created in the SQL Server by the SQL Server Administrator.
So, While migrating the Logins : Key points to remember is
1. For Windows Login -- Grant Permission is needed
2. For SQL Login -- Login Created is needed
Script is :
For Windows Authenticated Logins:
SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,
' EXEC sp_defaultdb @loginame = ''' + loginname + '''',',
@defdb = ''' + dbname + ''''
FROM sys.syslogins
where name in
(
select name from sys.server_principals where type in ('U')
and name not like '%\SYSTEM'
)
' EXEC sp_defaultdb @loginame = ''' + loginname + '''',',
@defdb = ''' + dbname + ''''
FROM sys.syslogins
where name in
(
select name from sys.server_principals where type in ('U')
and name not like '%\SYSTEM'
)
Execute the above script in Source server and copy the output by selecting all the data without header to DESTINATION Server and execute it. Thats it, Windows login migration from Source server to Destination server is done.
For SQL Authenticated Logins:
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + '''',',
@defdb = ''' + dbname + '''' ,', @deflanguage = ''' + language + '''',',
@encryptopt = ''skip_encryption''',',
@passwd =', cast(password AS varbinary(256)),',
@sid =', sid
FROM
sys.syslogins
where name in
(
select name from sys.server_principals where type in ('S') and name not like 'sa'
)
GO
@defdb = ''' + dbname + '''' ,', @deflanguage = ''' + language + '''',',
@encryptopt = ''skip_encryption''',',
@passwd =', cast(password AS varbinary(256)),',
@sid =', sid
FROM
sys.syslogins
where name in
(
select name from sys.server_principals where type in ('S') and name not like 'sa'
)
GO
Execute the above script in Source server and copy the output by selecting all the data without header to DESTINATION Server and execute it. Thats it, SQL logins migration from Source server to Destination server is done.
Hope the above helps
Thanks :)
