July 7, 2015 at 10:01 am
I'm using SQL Server 2012 R2 and am working on configuring vendor access to a particular DB. I have a test db & (what will eventually be) the production DB. I've configured security for the test DB and want to back that up, then restore it (including all settings) to the prod one, renaming it to the prod DB name. Can anyone help me with the basic steps to accomplish this? I've tried a couple of things but haven't been able to get the security to copy over. Or, is there a better way of doing this? Apreciate any help!
July 7, 2015 at 1:54 pm
It might be easiest for you to use a free tool like Permissions Extractor from Idera.
https://www.idera.com/productssolutions/freetools/sqlpermissionsextractor
July 8, 2015 at 10:53 am
Thanks Jeremy, I was able to accomplish this by doing a restore from a test backup to the prod db and changing the destination file names to reflect the new db name. Not sure if it's going to play out going forward but I'll cross those bridges when they come up. This is turning out to be quite the learning opportunity. Thanks again for the suggestion.
July 9, 2015 at 8:57 am
Assuming you've ensured that your test DB restore to production will never overwrite an active production db...
You can script this out into a job. It's just a simple T-SQL restore statement using WITH MOVE for the file names, then running sp_change_users_login against the database. Be sure you've copied up all security on the server level so your database security isn't orphaned when you make the final move.
Here's the script we use for fixing the users. I don't remember where we got it from. It's fairly simple and fixes the SID issue between different instances / environments.
/*-----------------------------------------------------------------------------
After restoring a database,fix users that have a corresponding login on this server
-----------------------------------------------------------------------------*/
-------------------------------------------------------------------------------
-- Declarations
-------------------------------------------------------------------------------
DECLARE
@User_Namevarchar(255),-- User Name
@Adhoc_SQLnvarchar(2000)-- Used with sp_ExecuteSQL
-------------------------------------------------------------------------------
-- Declare Cursor
--Only Users that need fixing that also have a valid login on this server
-------------------------------------------------------------------------------
DECLARE User_Cursor CURSOR FOR
SELECT
DP.[name]
FROM
sys.database_principals DP
INNER JOIN
sys.server_principals SP
ON
DP.[name] = SP.[name]
WHERE
DP.type_desc = 'SQL_USER'
AND
(DP.sid IS NOT NULL ANDDP.sid <> 0x0)
AND
SUSER_SNAME(DP.sid) IS NULL
-------------------------------------------------------------------------------
-- Open Cursor of Users
-------------------------------------------------------------------------------
OPEN User_Cursor
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
-------------------------------------------------------------------------------
-- Process Each User and issue Fix command via Sp_Change_Users_Login
-------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build Command String
SET @Adhoc_SQL = 'EXEC sp_change_users_login '+
'@Action = ''Auto_Fix'', ' +
'@UserNamePattern = ''' + @User_Name + ''''
-- Display Command issued
print @Adhoc_SQL
-- Execute Command
EXEC sp_ExecuteSQL @Adhoc_SQL
-- Get the next Row
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
END
-------------------------------------------------------------------------------
-- Cursor Cleanup
-------------------------------------------------------------------------------
CLOSE User_Cursor
DEALLOCATE User_Cursor
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply