Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2012 - Database Move to new server - Unknown SQL User password

Posted on 2016-09-18
10
Medium Priority
?
71 Views
Last Modified: 2016-10-23
Hi all,
Ive been asked to move a DB to a new server.

Current and new SQL servers are running Server2012R2, both members of the domain, and both have identical versions SQL versions 11.0.5613.  

The DB has 2 x local SQL users - And you guessed it,  no one knows the passwords for these accounts.

Is there any way of showing these passwords, or exporting\importing the SQL users from one SQL Server to another?.

Many thanks,
String
0
Comment
Question by:TreadStone_IT
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41804136
You can take backup and restore the back somewhere else. There you will get everything including users.

There is not way you can view password of other people.
0
 

Author Comment

by:TreadStone_IT
ID: 41804143
Hi Pawan, Thanks for taking time to comment on my problem.

I have tried backup and restore of database on new server however that leaves orphan'ed users in the DB.
ie
DB references a SQL user that new server doesnt know about.
Any tips?
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41804151
Boss

Here is what you need..

Resolve an Orphaned User
In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:
CREATE LOGIN <login_name>  
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;  

To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.
ALTER USER <user_name> WITH Login = <login_name>;  

When you recreate a missing login, the user can access the database using the password provided. Then the user can alter the password of the login account by using the ALTER LOGIN statement.
ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';  

System_CAPS_ICON_important.jpg Important

Any login can change it's own password. Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. However, only members of the sysadmin role can modify passwords of sysadmin role members.

https://msdn.microsoft.com/en-IN/library/ms175475.aspx

Pls let me know if you need more help on this.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41804163
Sample Code..

--

--

USE DBNAME;
GO

CREATE LOGIN sqlUser
	WITH PASSWORD = 'P@ssword', DEFAULT_DATABASE = DBNAME;
GO

CREATE USER sqlUser
FOR LOGIN sqlUser;
GO

DROP LOGIN sqlUser;


CREATE LOGIN sqlUser
	WITH PASSWORD = 'P@ssword', DEFAULT_DATABASE = DBNAME;
GO

--

Open in new window


--
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41804166
You should be able to back up and restore the master database along with the application database(s), which is where all those logins and passwords are stored. It has been many years since I last did that, and I don't know what limitations there may be today, but as long as the versions are exactly the same it should be possible. It might be good to keep the file paths the same as well -- something is trying to come back to me about that but it hasn't so far.

When I did this, when I reached the 'restore' part I followed the steps from a TechNet article like this one. Here is another take on the subject.

You could still have orphaned users in the application databases afterward, but you can reconnect them to their logins using sp_change_users_login. I normally use the 'update_one' option, but auto_fix might be useful in this situation.

I believe I have also, in the distant past, managed to restore msdb, so as to not have to set up all the jobs again. model can be restored too. I used an article like this as a guide.

Have you considered virtualization? :-)
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41804169
I am assuming, by the way, that you aren't using contained databases.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41804834
Use this code to generate the create login statements with password information from the source server. You copy the output of this script and you execute it in the new server:
-- ##### The script will create 2 sps in master db and then will execute the sp_help_revlogin procedure to generate scripts for all logins
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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

EXECUTE sp_help_revlogin

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41828019
Any feedback about this question? Please check the answers.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41846259
The DB has 2 x local SQL users - And you guessed it,  no one knows the passwords for these accounts.
Are the users being used from an application? If so the application has those passwords stored and you just need to find if it's encrypted or not.
0
 

Author Closing Comment

by:TreadStone_IT
ID: 41856406
Hi all,
This migration all went well.
I used Zberteoc's script which ran well and achieved the result.  I did research what the script did and how to use it before running it.

More background info referenced here.
Method 1
https://support.microsoft.com/en-au/kb/246133

Many thanks to all who contributed.
String
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question