Orphaned Users
-
Is there an alternative to running sp_change_users_login to fix each orphaned user because I have almost a hundred orphaned users!
Thanks
-
You can update the system tables directly.
-
Hi Kory,
Please can you explain how to this?
Thanks
-
Why wont sp_change_users_login work for you? What are your permissions on the server/database?
If you have 'sysadmin' role I recommend using 'auto fix'. If you are dbo in the database referenced I recommend generating dynamic sql per user using the 'Update_One' option
You must have 'sysadmin' for this method and updating system objects outside of delivered procedures is dangerous and should not be done. I would not conduct it without an experienced understanding of what you are doing and have a good backup!
sp_configure 'allow updates', 1
Go
Reconfigure With OverRide
Go
/* Update dummy db users sid to login sid */
Update dbo.sysusers
Set sid = sl.sid
From master.dbo.syslogins sl
Inner Join userdb.dbo.sysusers su
On sl.name = su.name
Where su.name = 'dummy'
sp_configure 'allow updates', 0
Go
Reconfigure With OverRide
Go
-
Select 'Exec sp_change_users_login ''Update_One'','''+su.name+''','''+su.name+''''
From master.dbo.syslogins sl
Inner Join dbo.sysusers su
On sl.name = su.name
Where su.uid > 2
-
IMHO it would be better to write a loop that would start sp_change_users_login for each user. I found one script that we used on a DB restored for testing purposes. Not sure whether this is precisely what you need, but it should at least help you to understand what I mean:
DECLARE @name AS varchar(50)
DECLARE cr_logins CURSOR FOR
SELECT name FROM master..syslogins WHERE name IS NOT NULL
OPEN cr_logins
FETCH NEXT FROM cr_logins INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name
EXEC master..sp_change_users_login 'update_one', @name, @name
FETCH NEXT FROM cr_logins INTO @name
END
CLOSE cr_logins
DEALLOCATE cr_logins
BTW, this is exception, otherwise I avoid cursors - but it makes little difference what you use to start a simple sp over and over.
EDIT : I didn't see the last post by Kory when I wrote this one, and was referring to the updating of system objects when I said that this would be better. If Kory's last solution works, then don't bother to read my post at all
-
Thank you both, works perfectly.
Regards
-
Hey, Great code here - feeding the userlist without use of a cursor, I love that! However, a question: Isn't this running the procedure for all users whether or not they need relinking? ie. Ultimately, we would only run this procedure for the list of users returned using the 'Report' option. I am researching the system values now to determine how I could do this, but do you have a way to limit this calll to only run on unlinked users, the ones returned in the 'Report' option of this proc?
-
That is correct edit the Master or if it is from a restore operation rerestore it is faster than editing the Master. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
I wanted to pass something by everyone - I think I have a pretty good way to handle all databases on a server. One difficulty with this of course is the blasted restriction on the "USE" command in SPs. However, there is the undocumented sp_MSForEachDB proc that can, in many cases, get around this.
This is the simple proc that makes the call - I chose autofix instead of the update_one since I want to handle updates to the logins and use the sysuser database as the "master" not the syslogins. Also, one important "trick" if you are not aware of it. You must change databases based on the current parm value of the msForEachDb proc, otherwise your proc will execute once for each database but for the same database! the USE command with the "?" directive delimited by semicolon for multiple commands works just fine here. (I included a display on the dbname as well). This pattern should be handy to anyone needing to loop through databases and run procs that need to work on the "current" database. To my knowledge, the "unlinked" users are simply users with a different sid than the master. We simply want to match it, and this accomplished this nicely. Comments appreciated.
First, the driver procedure to loop through the databases -
----------------------------------------------------------
Create PROC uspSearchDBServerForOrphans
AS
Exec sp_MSForEachDB 'USE ?;select db_name();EXEC uspSearchForOrphans '
----------------------------------------------------------
CREATE PROC uspSearchDBServerForOrphans
AS
declare @UserName varchar(50)
declare #curUsers CURSOR
For
select SU.name from Sysusers SU
Right Outer Join master.dbo.syslogins SL
On SL.name = SU.name
and SU.sid <> SL.sid -- Same rowset as 'Report' option
where su.uid > 2
open #curUsers
fetch next from #curUsers into @UserName
while @@fetch_status = 0
begin
exec sp_change_users_login @action = 'auto_fix', @usernamepattern = @UserName
fetch next from #curUsers into @UserName
end
close #curUsers
deallocate #curUsers
---
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply