how to save logins and permission prior a db refresh

Hi Experts, I have a question about saving security login and permissions.
I have to refresh a test db with prd data. I understand I need to have the permissions of the logins so I can change them once the db is refreshed beacuse the permissions on the prd db are different than the permissions on the uat db. I have to save them prior the refresh and apply them after the refresh.

I got this script but when I run it on the uat db I get only 3 rows for dbo, a dba and a dbateam. I need to get logins of the developers and users of uat also but I don't get them. I don't understand why.
script:
select 'exec sp_change_users_login ''update_one'',
''' + a.name + ''' , ''' + a.name + ''''
from sysusers a, sysxlogins b where a.issqlrole = 0
and a.sid = b.sid

Result:
exec sp_change_users_login 'update_one',  'dbo' , 'dbo'
exec sp_change_users_login 'update_one',  'DRDEKKER' , 'DRDEKKER'
exec sp_change_users_login 'update_one',  'dba_cur' , 'dba_cur'

Can someone please help me out here so that I can start the restore of the uat DB?

sharschoAsked:
Who is Participating?
 
NightmanConnect With a Mentor CTOCommented:
Hi sharscho

The source code for the procedure is in the article. I'll post it here below:

----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
      EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN
        -- Null password
      EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
      ELSE
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
 ----- End Script -----

You run this on Server A (which creates the porocedure), and then run
EXEC master..sp_help_revlogin
on Server A

copy the results (which is a script that is generated by the procedure) and run it against server B
0
 
NightmanCTOCommented:
Have a look at this article from MS on how migrate logins and passwords between instances of SQL Server:
http://support.microsoft.com/kb/246133/
0
 
sharschoAuthor Commented:
But this artucle talks about different versions of sqlserver, I have the same version it is only a refresh of the uat db with prd data.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
NightmanCTOCommented:
This article also talks about between instances of SQL Server. Are these not different servers?
0
 
sharschoAuthor Commented:
Yes they are on different servers. I am going to look again at the article.
0
 
sharschoAuthor Commented:
And we have sqlserver 2000 installed.
0
 
NightmanCTOCommented:
What version of SQL Server are you running?
That article shows how to move between the following:
SQL 7 to SQL 7
SQL 7 to SQL 2000
SQL 2000 to SQL 2000
SQL 2000 to SQL 2005

and there is another link (from that KBA):
SQL 2005 to SQL 2005
http://support.microsoft.com/kb/918992/
0
 
sharschoAuthor Commented:
Nightman, the databases are on different servers but I need to save the logins and permissions of server A so that when the refresh with the data of server B is done, I can apply the logins again onto this new DB. and so there is no destination server. I jsut need to mantain the security settings that are on server A right now so that when users login they don't get errors beacuse of the db refresh and also the permission of the prd db can not be same for the uat db. Hope it is more clear now.
0
 
NightmanCTOCommented:
Yes, that is what I thought.

the process is that on Server A, you create the sp_help_revlogin procedure.
then run EXEC master..sp_help_revlogin on Server A

The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination Server B.

Then you restore the database on Server B and those same accounts should have permission to access the database on Server B
0
 
ziolkoCommented:
sharscho using method described in article posted by Nightman
you can "dump" security info to file and then restore it on any mssql including originating one, in other words it's like login backup.

ziolko.
0
 
sharschoAuthor Commented:
OK so I use the sp_helplogins on the uat server. save the output to a file. Do the restore of the prd db on the uat server. then run the output in query analyzer so that we can get back the same permissions in the new restored db? Am I right?

I did look at it and it looks good let me knwo if this is correct.
0
 
NightmanCTOCommented:
That is correct.
0
 
sharschoAuthor Commented:
Where do I get this revlogins, I don't have it my stored procedures....
0
 
sharschoAuthor Commented:
I asked where I get the help_revlogins because when I run the helplogins I just get the list of users and permissions, I don't get scripts to create the login again afterwards. I can create them with the info I get from the report but I wanted a script to run is quesry analyzer like you mentioned before.
0
 
sharschoAuthor Commented:
Cool Thanks nightman, you are a genius and you saved my day. I do have the script now and I am going to start the restore now. Thank you again!
0
 
NightmanCTOCommented:
It's an absolute pleasure ;)

Please post back here if you run into any problems relating to this code - I will be online on and off during the course of the day (my time zone is GMT+2), and will check in periodically to see if everything is still running OK.
0
 
sharschoAuthor Commented:
Nightman, can you help with the logfile problem I have? I need to know how I can place the translog file in another location where there is more space. I did post a question but I don't get answers that can help me. Thank you.
0
All Courses

From novice to tech pro — start learning today.