0

There is a user account that someone set up on one of our database servers long ago, that no-one any longer knows the password for. However some apps have the password (for various reasons we cannot recover the password from the apps).

Unfortunately we want to point the apps at a new server and will backup and restore the relevant databases to this new server - is there a way to copy the login from one server to the other keeping the password value as whatever it was before?

2
  • SQL Server or Windows account?
    – Richard
    May 25, 2012 at 11:21
  • SQL Server account
    – Richard
    May 25, 2012 at 11:25

2 Answers 2

2

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

Relevant content from link:

In this article, server A and server B are different servers. Additionally, both server A and server B are running SQL Server 2005.

Note This information also applies to SQL Server 2008.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, the users may not be able to log in to the database on server B. Additionally, the users may receive the following error message: Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456) This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.

To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps: On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database. Open a new Query Editor window, and then run the following script. 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 Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure. Run the following statement. EXEC sp_help_revlogin The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section. Open a new Query Editor window, and then run the output script that is generated in step 3.

2
  • Hi MichelZ! Welcome to ServerFault. A quick note; because links can die or change over time, the encouraged behavior is to post a summary of the information along with the link in your answers. That way the answer itself is on this site and the link is a reference citation, and if something changes the answer here stays relevant. If you could edit your answer to update with that information it would be great :-) May 25, 2012 at 12:23
  • Thx, will do that in the future :)
    – MichelZ
    May 26, 2012 at 17:10
1

The better solution would be to change the password and then reconfigure the apps to use the new password. You stated that you can't recover the password from the apps but does that also mean that you can't reconfigure the apps to use another password? What if you need to migrate the apps?

Not knowing the password is a problem. In my opinion, this is untenable and unacceptable from an organization and administration standpoint. If it means doing some work to reconfigure the apps to use a new password then that's what should be happening.

Granted, in the short term you can follow the steps laid out in the link that MichelZ provided, but long term you should be figuring out how to address this and fix it.

2
  • Yes - I fully agree that we should replace a forgotten password and will do so once all hope of recovering the password is lost. Right now, however, there is still a glimmer of hope that we might be able to recover the original password (the person who probably originally set it is back from leave next week) - but not in time for the database moving which has to be done this weekend
    – Richard
    May 25, 2012 at 16:08
  • Gotcha. Good luck.
    – joeqwerty
    May 25, 2012 at 16:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .