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.
