Fix orphaned users in SQL Server
Ryan Rinaldi 7/18/2008 6:56:06 AMEvery time I restore a database, I forget how to fix the orphaned users. So I google, trying to find the magic words that will bring me to that stored procedure that I know exists, but I just can't remember the name of. For those of you who don't know, sp_change_users_login has an 'Auto_Fix' action that will map a user in a database to a login of the same name. Since I'm going to forget all that again, I'm posting this little snippet of Books Online. Maybe Google will help me find it next time!
sp_change_users_login
Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.
Syntax
sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
Arguments
[@Action =] 'action'
Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.
| Value | Description |
|---|---|
| Auto_Fix | Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended. user must be a valid user in the current database, and login must be NULL, a zero-length string (''), or not specified. |
| Report | Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login. user and login must be NULL, a zero-length string (''), or not specified. |
| Update_One | Links the specified user in the current database to login. login must already exist. user and login must be specified. |
[@UserNamePattern =] 'user'
Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can be used only with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users.
[@LoginName =] 'login'
Is the name of a SQL Server login. login is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
says:
5.04.2004 at 10:13 PMGood stuff Ryan! This one definately comes in handy.
Using roles helps mitigate this problem as well since they dont get "disconnected" when you restore.
says:
5.20.2004 at 7:30 PMdeclare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs
says:
5.20.2004 at 7:31 PMUSE 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
says:
2.23.2005 at 9:41 PMI used the Stored Procs that Juan suggested recently. They worked to perfection. I highly recommend them.