Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio.

Whenever I do this, the applications on the target machine cannot access the database until I delete the user they use, "george", from the database users (Database, Security, Users in SQL Server Management Studio), and re-add it as owner under Security, Logins, george/properties, user mapping.

Is there a better way to do this? It seems a little convoluted.

share|improve this question
1  
The SIDs of the logins don't match between the servers. Manually specify the SID in the CREATE LOGIN statement. – Jon Seigel Apr 24 '13 at 22:44
up vote 18 down vote accepted

This is the difference between logins and users and how they relate to each other:

  • Logins - Instance level principals that allow an entity to connect to the SQL Server instance. They do not, by their nature, grant any access to databases on the instance. The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.
  • Users - Database level principals that allow an entity to connect to a SQL Server database. Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected. You can fix this with the following SQL:

ALTER USER [foo] WITH LOGIN=[foo]

You can use the following query in the context of your database to check for orphans:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name
share|improve this answer
    
Thanks Mike this script shows the users of the data base, but how to copy the users to logins ? – Ahmad saleh Aug 5 '15 at 8:43
    
This is a really good explanation and answer to this issue, this really helped me. Thanks – Mark Walters Nov 18 '15 at 10:36

You could look into changing the database to contained database. Contained database user are authenticated by the database, not at instance level through login. It makes moving database to different instance simpler.

If not, you could backup the login information using sp_help_revlogin scripts provided at this Microsoft support KB. And execute the output script on the new instance.

share|improve this answer

I use the script below to migrate/create the user accounts and logins. Run it from the Server that you have restored the database to and supply the original Server name as a parameter to the procedure.

I don't take any credit for the procedure as I obtained it from elsewhere but it works fine.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[stp_Admin_ReplicateUserLogins]    Script Date: 10/29/2015 08:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[stp_Admin_ReplicateUserLogins] --'OriginalSourceDatabase', 1
    @PartnerServer sysname,
    @Debug bit = 0 -- 0 = Create Users, 1 = Display SQL command but doesn't execute query.
As

Declare @MaxID int,
    @CurrID int,
    @SQL nvarchar(max),
    @LoginName sysname,
    @IsDisabled int,
    @Type char(1),
    @SID varbinary(85),
    @SIDString nvarchar(100),
    @PasswordHash varbinary(256),
    @PasswordHashString nvarchar(300),
    @RoleName sysname,
    @Machine sysname,
    @PermState nvarchar(60),
    @PermName sysname,
    @Class tinyint,
    @MajorID int,
    @ErrNumber int,
    @ErrSeverity int,
    @ErrState int,
    @ErrProcedure sysname,
    @ErrLine int,
    @ErrMsg nvarchar(2048)
Declare @Logins Table (LoginID int identity(1, 1) not null primary key,
                        [Name] sysname not null,
                        [SID] varbinary(85) not null,
                        IsDisabled int not null,
                        [Type] char(1) not null,
                        PasswordHash varbinary(256) null)
Declare @Roles Table (RoleID int identity(1, 1) not null primary key,
                    RoleName sysname not null,
                    LoginName sysname not null)
Declare @Perms Table (PermID int identity(1, 1) not null primary key,
                    LoginName sysname not null,
                    PermState nvarchar(60) not null,
                    PermName sysname not null,
                    Class tinyint not null,
                    ClassDesc nvarchar(60) not null,
                    MajorID int not null,
                    SubLoginName sysname null,
                    SubEndPointName sysname null)

Set NoCount On;

If CharIndex('\', @PartnerServer) > 0
  Begin
    Set @Machine = LEFT(@PartnerServer, CharIndex('\', @PartnerServer) - 1);
  End
Else
  Begin
    Set @Machine = @PartnerServer;
  End

-- Get all Windows logins from principal server
Set @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) +
        'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
        'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) +
        'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And P.name <> ''sa''' + CHAR(10) +
        'And P.name Not Like ''##%''' + CHAR(10) +
        'and P.Name Not like ''NT SERVICE%''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', P.name) = 0;';

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
Exec sp_executesql @SQL;

-- Get all roles from principal server
Set @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) +
        'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_role_members RM' + CHAR(10) +
        'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals RoleP' +
        CHAR(10) + char(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) +
        'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals LoginP' +
        CHAR(10) + char(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) +
        'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And LoginP.name <> ''sa''' + CHAR(10) +
        'And LoginP.name Not Like ''##%''' + CHAR(10) +
        'And LoginP.name Not Like ''NT SERVICE%''' + CHAR(10) +
        'And RoleP.type = ''R''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;';

Insert Into @Roles (RoleName, LoginName)
Exec sp_executesql @SQL;

-- Get all explicitly granted permissions
Set @SQL = 'Select P.name Collate database_default,' + CHAR(10) +
        '   SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) +
        '   SubP.name Collate database_default,' + CHAR(10) +
        '   SubEP.name Collate database_default' + CHAR(10) +
        'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
        'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_permissions SP' + CHAR(10) +
        CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) +
        'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals SubP' + CHAR(10) +
        CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) +
        'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.endpoints SubEP' + CHAR(10) +
        CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) +
        'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And P.name <> ''sa''' + CHAR(10) +
        'And P.name Not Like ''##%''' + CHAR(10) +
        'And P.name Not Like ''NT SERVICE%''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', P.name) = 0;'

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
Exec sp_executesql @SQL;



Select @MaxID = Max(LoginID), @CurrID = 1
From @Logins;

While @CurrID <= @MaxID
  Begin
    Select @LoginName = Name,
        @IsDisabled = IsDisabled,
        @Type = [Type],
        @SID = [SID],
        @PasswordHash = PasswordHash
    From @Logins
    Where LoginID = @CurrID;

    If Not Exists (Select 1 From sys.server_principals
                Where name = @LoginName)
      Begin
        Set @SQL = 'Create Login ' + quotename(@LoginName)
        If @Type In ('U', 'G')
          Begin
            Set @SQL = @SQL + ' From Windows;'
          End
        Else
          Begin
            Set @PasswordHashString = '0x' +
                Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');

            Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED, ';

            Set @SIDString = '0x' +
                Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
            Set @SQL = @SQL + 'SID = ' + @SIDString + ';';
          End

        If @Debug = 0
          Begin
            Begin Try
                Exec sp_executesql @SQL;
            End Try
            Begin Catch
                Set @ErrNumber = ERROR_NUMBER();
                Set @ErrSeverity = ERROR_SEVERITY();
                Set @ErrState = ERROR_STATE();
                Set @ErrProcedure = ERROR_PROCEDURE();
                Set @ErrLine = ERROR_LINE();
                Set @ErrMsg = ERROR_MESSAGE();
                RaisError(@ErrMsg, 1, 1);
            End Catch
          End
        Else
          Begin
            Print @SQL;
          End

        If @IsDisabled = 1
          Begin
            Set @SQL = 'Alter Login ' + quotename(@LoginName) + ' Disable;'
            If @Debug = 0
              Begin
                Begin Try
                    Exec sp_executesql @SQL;
                End Try
                Begin Catch
                    Set @ErrNumber = ERROR_NUMBER();
                    Set @ErrSeverity = ERROR_SEVERITY();
                    Set @ErrState = ERROR_STATE();
                    Set @ErrProcedure = ERROR_PROCEDURE();
                    Set @ErrLine = ERROR_LINE();
                    Set @ErrMsg = ERROR_MESSAGE();
                    RaisError(@ErrMsg, 1, 1);
                End Catch
              End
            Else
              Begin
                Print @SQL;
              End
          End
        End
    Set @CurrID = @CurrID + 1;
  End

Select @MaxID = Max(RoleID), @CurrID = 1
From @Roles;

While @CurrID <= @MaxID
  Begin
    Select @LoginName = LoginName,
        @RoleName = RoleName
    From @Roles
    Where RoleID = @CurrID;

    If Not Exists (Select 1 From sys.server_role_members RM
                Inner Join sys.server_principals RoleP
                    On RoleP.principal_id = RM.role_principal_id
                Inner Join sys.server_principals LoginP
                    On LoginP.principal_id = RM.member_principal_id
                Where LoginP.type In ('U', 'G', 'S')
                And RoleP.type = 'R'
                And RoleP.name = @RoleName
                And LoginP.name = @LoginName)
      Begin
        If @Debug = 0
          Begin
            Exec sp_addsrvrolemember @rolename = @RoleName,
                            @loginame = @LoginName;
          End
        Else
          Begin
            Print 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''',';
            Print '     @loginame = ''' + @LoginName + ''';';
          End
      End

    Set @CurrID = @CurrID + 1;
  End

Select @MaxID = Max(PermID), @CurrID = 1
From @Perms;

While @CurrID <= @MaxID
  Begin
    Select @PermState = PermState,
        @PermName = PermName,
        @Class = Class,
        @LoginName = LoginName,
        @MajorID = MajorID,
        @SQL = PermState + space(1) + PermName + SPACE(1) +
            Case Class When 101 Then 'On Login::' + QUOTENAME(SubLoginName)
                    When 105 Then 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
                    Else '' End +
            ' To ' + QUOTENAME(LoginName) + ';'
    From @Perms
    Where PermID = @CurrID;

    If Not Exists (Select 1 From sys.server_principals P
                Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
                Where SP.state_desc = @PermState
                And SP.permission_name = @PermName
                And SP.class = @Class
                And P.name = @LoginName
                And SP.major_id = @MajorID)
      Begin
        If @Debug = 0
          Begin
            Begin Try
                Exec sp_executesql @SQL;
            End Try
            Begin Catch
                Set @ErrNumber = ERROR_NUMBER();
                Set @ErrSeverity = ERROR_SEVERITY();
                Set @ErrState = ERROR_STATE();
                Set @ErrProcedure = ERROR_PROCEDURE();
                Set @ErrLine = ERROR_LINE();
                Set @ErrMsg = ERROR_MESSAGE();
                RaisError(@ErrMsg, 1, 1);
            End Catch
          End
        Else
          Begin
            Print @SQL;
          End
      End

    Set @CurrID = @CurrID + 1;
  End


Set NoCount Off;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.