June 24, 2015 at 9:20 pm
How to get all logins & users with their password into other server while doing migration?
June 25, 2015 at 5:58 am
there's a Microsoft proc you can download that extracts users and their hashed passwords named sp_help_revlogin;
you use it for exactly what you are asking copying/transferring existing windows users/groups/SQL users to another machine.
i would recommend grabbing this enhanced version instead, which also grabs any roles those same logins are members of...
that way, when you copy someone, and they were sysadmin on the old machine, they have the same permissions on the new one.
http://www.tinyint.com/index.php/2009/07/08/sp_help_revlogin-cleaned-up-and-revised-with-roles/
Lowell
June 25, 2015 at 5:59 am
which versions of sql server are you moving between?
This is my script I use but wont work from sql2008 -> sql 2012 as the encryptor changed
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @uid INT
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ''
--=============================================
--First get the SQL logins
--=============================================
SELECT principal_id, [type] INTO #sqlusers FROM sys.server_principals WHERE [type] = 'S' AND principal_id > 4
IF (SELECT COUNT(*) FROM #sqlusers) = 0
BEGIN
SELECT @sql = @sql + '/*No SQL Logins have been found on ' + @@SERVERNAME + '*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all SQL server logins' + CHAR(10) +
'===================================================================================' + CHAR(13) +
'Note: these are the users found in the instance ' + @@SERVERNAME + ', but they may not all be valid, check them first*/' +
CHAR(13) + CHAR(13)
PRINT @sql
SELECT @sql = ''
SELECT @sql = @sql + 'USE [master]' + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
WHILE (SELECT TOP 1 principal_id FROM #sqlusers) IS NOT NULL
BEGIN
SELECT TOP 1 @uid = principal_id FROM #sqlusers
SELECT @sql = @sql + 'IF (SELECT name FROM sys.server_principals WHERE name = ''' +
SUSER_NAME(principal_id) + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) +
'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
', CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE ';' + CHAR(10) + 'ALTER LOGIN [' + name + '][ DISABLE;'
END +
CHAR(10) + 'END'
FROM master.sys.sql_logins
WHERE name NOT LIKE '##%' AND name <> 'sa' and
principal_id = @uid
PRINT @sql + CHAR(10)
DELETE FROM #sqlusers WHERE principal_id = @uid
SELECT @sql = ''
END
DROP TABLE #sqlusers
END
SELECT @sql = ''
--=========================================================================
--Now get the Windows logins, not accounting for certificated logins here
--=========================================================================
SELECT principal_id, [type] INTO #winusers FROM sys.server_principals WHERE [type] IN ('U', 'G') AND principal_id > 4
IF (SELECT COUNT(*) FROM #winusers) = 0
BEGIN
SELECT @sql = @sql + '/*No Windows Logins have been found on ' + @@SERVERNAME + '*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @sql = '/*Scripting all Windows server logins' + CHAR(10) +
'===================================================================================' + CHAR(13) +
'Note: these are the users found in the instance ' + @@SERVERNAME + ', but they may not all be valid, check them first*/' +
CHAR(13) + CHAR(13)
PRINT @sql
SELECT @sql = ''
SELECT @sql = @sql + 'USE [master]' + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
WHILE (SELECT TOP 1 principal_id FROM #winusers) IS NOT NULL
BEGIN
SELECT TOP 1 @uid = principal_id FROM #winusers
SELECT @sql = @sql + 'IF (SELECT name FROM sys.server_principals WHERE name = ''' +
SUSER_NAME(principal_id) + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) +
'CREATE LOGIN [' + name + '] FROM WINDOWS ' +
'WITH DEFAULT_DATABASE = ' + ISNULL(QUOTENAME(default_database_name), '[master]') +
', DEFAULT_LANGUAGE = ' + ISNULL(default_language_name, 'British') +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '][ DISABLE;'
END +
CHAR(10) + 'END'
FROM sys.server_principals WHERE [type] IN ('G', 'U')
AND name NOT LIKE '##%' AND principal_id = @uid
PRINT @sql + CHAR(10)
DELETE FROM #winusers WHERE principal_id = @uid
SELECT @sql = ''
END
DROP TABLE #winusers
END
SELECT @sql = ''
--==============================================
--Now get all the server role assignments
--==============================================
SELECT sp.principal_id, sp.name as lname, sp2.name as rname INTO #users FROM sys.server_principals sp
INNER JOIN sys.server_role_members sr ON sp.principal_id = sr.member_principal_id
INNER JOIN sys.server_principals sp2 ON sr.role_principal_id = sp2.principal_id
WHERE sp.[type] IN ('S', 'U', 'G') AND sp.principal_id > 4
SELECT @sql = '/*Server role assignments*/' + CHAR(13)
PRINT @sql
SELECT @sql = ''
SELECT @sql = 'USE [master]' + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
WHILE (SELECT TOP 1 principal_id FROM #users) IS NOT NULL
BEGIN
SELECT TOP 1 @uid = principal_id FROM #users
SELECT@sql = @sql + 'exec sp_addsrvrolemember ''' +
lname + ''', ''' +
rname + '''' + CHAR(13)
FROM #users WHERE principal_id = @uid
PRINT @sql + CHAR(10)
DELETE FROM #users WHERE principal_id = @uid
SELECT @sql = ''
END
DROP TABLE #users
SELECT @sql = ''
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 25, 2015 at 6:12 am
Thank you.
When you execute Revlogin
How to get all logins @ same time. Looks we need each login name as parameter
June 25, 2015 at 6:18 am
try either procedure i offered, or the one Perry suggested.
when called with no parameters, it returns all logins.
Lowell
June 25, 2015 at 7:19 am
ramana3327 (6/25/2015)
Thank you.When you execute Revlogin
How to get all logins @ same time. Looks we need each login name as parameter
you'll find my script a lot easier 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 25, 2015 at 11:39 am
Hi Perry,
Is the passwords are same as the original passwords?
One more question about db mapping. Is it automatically map the database access to
June 29, 2015 at 5:48 am
ramana3327 (6/25/2015)
Hi Perry,Is the passwords are same as the original passwords?
Yes, as I said above if you're moving between instances that sql server 2008 or lower your passwords are retained as hashes. If you move a login from 2008 or lower to 2012 the password will not be retained.
ramana3327 (6/25/2015)
One more question about db mapping. Is it automatically map the database access to
If you script the login it will retain the logins default database.
As the script also retains the logins SID from the source server, all database mappings will be fine, there will be no orphaned users
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply