June 25, 2015 at 12:23 pm
Hi,
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
June 25, 2015 at 1:07 pm
PJ_SQL (6/25/2015)
Hi,I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
Yes, you will. The users and permissions will remain intact, but the logins aren't at the database level and aren't part of the backup. I suggest checking out sp_change_users_login at https://msdn.microsoft.com/en-us/library/ms174378%28v=sql.110%29.aspx.
June 25, 2015 at 3:00 pm
But, that would work only for SQL Logins right?And there might be few AD accounts which are not in prod but in test which might have permission to the database.
June 26, 2015 at 5:27 am
For the AD logins, if both SQL Servers are on the same domain, the start of authority is the same, so your SIDs should match.
June 27, 2015 at 6:45 am
The following script will transfer logins and roles
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" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply