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.
We switched over to the DR DB Server. Our secondary relies on Log Shipping and so the pathway is straight forward:
Restore database [database-name] with recovery;
Restore database [DBBackup] with recovery;
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.
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
use payroll; EXEC master.dbo.sp_change_users_login ''update_one'', 'hrdb', 'hrdb'
But, no luck errors all over the place.
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108 Terminating this procedure. The User name 'hrdbuser' is absent or invalid.
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.
Using SSMS, brought up the user and noticed that it was created as “Without Login“.
ALTER USER with LOGIN
use [database]; ALTER USER [username] with LOGIN = [login-name];
use payroll; ALTER USER [hrdbuser] with LOGIN = [hrdbuser]
Here is a more elaborate script that targets all orphaned users.
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
use [HRDB] go exec [dbo].[sp_alignUsers] go
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.
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“.
I hope you work for good people and that you enjoy coming to work; as everything rests on that.