Sunday, January 1, 2012

Troubleshooting Orphaned Users

When you restore a database backup to another server, you may experience a problem with orphaned users. The Troubleshooting Orphaned Users' topic in SQL Server Books Online does not outline the exact steps for troubleshooting this problem.

Although the terms login and user are often used interchangeably, they are very different. A login is used for user authentication and a database user account is used for database access and permissions validation. Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to the SQL Server server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.

When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having "orphaned users."

When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.
  1. Add a login to the master database, and specify the default database as Northwind:

    Use master go sp_addlogin 'test', 'password', 'Northwind'
          
  2. Grant access to the user you just created:

    Use Northwind go sp_grantdbaccess 'test'
          
  3. Backup the database.

    BACKUP DATABASE Northwind
    TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
          
  4. Restore the database to a different SQL Server server:

    RESTORE DATABASE Northwind
    FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
          
    The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.
  5. Now, to detect orphaned users, run this code:

    Use Northwind go sp_change_users_login 'report'
          
    The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the Northwind database, and the sysxlogins system table in the master database.

Steps To Resolve Orphaned Users

  1. Run the following command for the orphaned user from the preceding step:

    Use Northwind
    go
    sp_change_users_login 'update_one', 'test', 'test'
          
    This relinks the server login "test" with the the Northwind database user "test". The sp_change_users_login stored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.
  2. After you run the code in the preceding step, the user can access the database. The user may then alter the password with the sp_password stored procedure:

    Use master 
    go
    sp_password NULL, 'ok', 'test'
          
    This stored procedure cannot be used for Microsoft Windows NT security accounts. Users connecting to a SQL Server server through their Windows NT network account are authenticated by Windows NT; therefore, their passwords can only be changed in Windows NT.

    Only members of the sysadmin role can change the password for another user's login.
Mostly, I use at work is following:

To find Orphan user in dbs..
  1. Click on db from folder view and then create a new query window
  2. Type Exec sp_change_users_login 'report', this will tell you which users are considerd as a orphan users.
  3. Once you have list of users, type Exec sp_change_users_login 'Auto_Fix', 'Username' when you dont have password, which is common.
  4. Execute query and Most of the time this will solve my orphan user issue.



Microsoft Reference Site: http://support.microsoft.com/kb/274188



Friday, December 30, 2011

How to transfer logins and passwords between instances of SQL Server? (SQL 7.0,2000,2005)

To do this, use one of the following methods.
Notes
  • The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.
  • The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.
  • Review the remarks at the end of this article for important information about the steps in the methods.
  • Method 2 assigns logins to roles.

Method 1

This method applies to the following scenarios:
  • You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
  • You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
  • You transfer logins and passwords between servers that are running SQL Server 2000.
To transfer logins and passwords between different versions of SQL Server, follow these steps:

Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored 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 ----- 
 
 
2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin 
   procedure from Query Analyzer on the source server. The sp_help_revlogin stored 
   procedure can be used on both SQL Server 7.0 and SQL Server 2000. 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 SQL Server. For example: 
 
   EXEC master..sp_help_revlogin
 

Remarks

  • Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
  • After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
         Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists                 in the current database.
 
 
 
 
 
Reference: http://support.microsoft.com/kb/246133