Background
Found myself woken up this morning due to Hard Server Crash. Wanted to get out of it and see if we can just have the Server Admins take care of it. But, Director says “No you are the DBA you need to be on-board, as well“.
So sleepily got up and it is showing.
Switched Over
We switched over to the DR DB Server. Our secondary relies on Log Shipping and so the pathway is straight forward:
Syntax
Restore database [database-name] with recovery;
Sample
Restore database [DBBackup] with recovery;
Align users
Everyone who has worked with SQL for a while knows that SQL Accounts created as native users, not Active Directory accounts, are misaligned when a database is restored on a new host.
Create Logins
Thankfully, we preserved our original Login creation scripts and so ran those on our new SQL Instance.
use master go if SUSER_ID('hrdbuser') is null begin create login [hrdbuser] with password = 'junepepper29#' , check_expiration=off , check_policy=off; end go exec sp_defaultdb [hrdbuser], [payroll] go use [payroll] go if user_id('hrdbuser') is null begin create user [hrdbuser] from login [hrdbuser] ; end
Align DB User To SQL Instance Login
sp_change_users_login
Code
use payroll; EXEC master.dbo.sp_change_users_login ''update_one'', 'hrdb', 'hrdb'
Error
But, no luck errors all over the place.
Text
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108 Terminating this procedure. The User name 'hrdbuser' is absent or invalid.
Image
Why the Error?
The error is so very new to me.
I checked everything sp_helplogins, sp_helpuser.
The login was present in the master and also had been created by Log Shipping in the contextual DB.
Database User
Using SSMS, brought up the user and noticed that it was created as “Without Login“.
ALTER USER with LOGIN
Snippet
Code
Syntax
use [database]; ALTER USER [username] with LOGIN = [login-name];
Sample
use payroll; ALTER USER [hrdbuser] with LOGIN = [hrdbuser]
Whole Script
Here is a more elaborate script that targets all orphaned users.
dbo.sp_alignUsers
use [master] go if object_id('dbo.[sp_alignUsers]') is null begin exec('create procedure dbo.[sp_alignUsers] as select 1/0') end go ALTER procedure [dbo].[sp_alignUsers] ( @scriptOnly bit = 0 , @SkipUsersWithoutLogin bit = 1 , @debug bit = 0 ) as begin set nocount on; declare @tblSystemUser TABLE ( [id] int not null identity(1,1) , [name] sysname not null ) declare @tblUser TABLE ( [id] int not null identity(1,1) , [name] sysname not null , [loginname] sysname null , [sid] varbinary(85) null , [gid] smallint not null ) declare @id int declare @idMax int declare @sql nvarchar(4000) declare @SQL_FORMAT nvarchar(4000) declare @login sysname declare @name sysname declare @sid varbinary(85) declare @sidLengthOfValidSID tinyint --set @SQL_FORMAT = 'EXEC master.dbo.sp_change_users_login ''update_one'', ''%s'', ''%s'' ' set @SQL_FORMAT = 'ALTER USER [%s] with LOGIN = [%s] ' set @sidLengthOfValidSID = 28 insert into @tblSystemUser ( [name] ) select 'dbo' union select 'guest' union select 'INFORMATION_SCHEMA' union select 'sys' insert @tblUser ( [name] , [loginname] , [sid] , [gid] ) select tblSU.name , tblSL.[loginname] , tblSL.[sid] , tblSU.[gid] from dbo.[sysusers] tblSU left outer join [master].dbo.[syslogins] tblSL on tblSU.[name] = tblSL.[name] --and tblSL.[sid] is null where tblSU.[gid] = 0 and tblSU.altuid is null and tblSU.[issqluser] = 1 and ( ( tblSU.[sid] is null ) or ( tblSU.[sid] != tblSL.[sid] ) ) and tblSU.[name] not in ( select [name] from @tblSystemUser) set @idMax = ( select max([id]) from @tblUser ) if (@debug = 1) begin print '@idMax '+ cast(@idMax as varchar(30)) end set @id = 1 while (@id <= @idMax) begin select @name = [name] , @login = [loginname] , @sid = [sid] from @tblUser tblU where tblU.[id] = @id if (@debug = 1) begin print 'Processing name ' + isNull(@name, '') + ' Login ' + isNull(@login, '') end if ( -- If OK to skip users with login ( ( @SkipUsersWithoutLogin = 0) ) or ( (@SkipUsersWithoutLogin = 1) and exists ( select 1 from [master].dbo.[syslogins] tblSL where tblSL.[loginname] = @login ) ) ) begin /* If user and login are valid, then prepare signature */ if ( ( @name is not null ) and ( @login is not null ) ) begin exec [master].dbo.xp_sprintf @sql output , @SQL_FORMAT , @name , @login print @sql end if (@scriptOnly= 0) begin exec( @sql) end end set @id = @id + 1 end end go exec sys.sp_MS_marksystemobject 'dbo.sp_alignUsers' go
invoke
use [HRDB] go exec [dbo].[sp_alignUsers] go
Error
The user cannot be remapped to a login
If you get the error pasted below:
Msg 33016, Level 16, State 1, Line 1 The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.
… It is likely you have yet to create a corresponding login in the master DB.
Quotes
One of the speakers I have been listening to lately is John Paul Jackson. He has original and inspiring ideas, but also quite witty.
There is a good pastor I tried listening to yesterday while making breakfast. But, though he has good material, I couldn’t hear through it for his monotone.
Here is one of the more memory quotes from John Paul Jackson. He said God called him after many years to leave his Pastoral Ministry and go be a Prophet yet again.
And, he replied that No, as he is now a Pastor, leading the Lord’s Flock. And, that God retorted that “Pastoring, notwithstanding, he has to go back into Prophecy, and that Paul was not very good has a Pastor in the first place, and that he, God, only put up with him for a while“.
Gratitude
I hope you work for good people and that you enjoy coming to work; as everything rests on that.