September 7, 2010 at 12:25 pm
Hello Everyone
I am looking for a script, better than the one that I have tried to create, that will drop all the orphan users that do not have a login. I have created a new Staging environment, performed a backup and restore of all the databases, there are hundreds, with hundreds of users in each. But now I am looking for a means to easily remove all the orphan users in each database.
Thank you in advance
Andrew SQLDBA
September 7, 2010 at 12:35 pm
Hi Andrew,
I did something some time ago to my old company, but is using Powershell. If you are interest, this is the link 🙂
September 7, 2010 at 12:39 pm
Thank You, but Sorry
The Upper Management said no using PS. They only want t-sql, so that anyone can come in behind me and be able to modify the code.
Thanks anyway
Andrew SQLDBA
September 7, 2010 at 12:45 pm
Hi Andrew,
I used a version of the following script to resync orphaned users. I made a couple of quick changes to it and came up with this:
USE database_name --Change to active database name
go
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being dropped'
--DROP USER @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
Be sure to change the "USE..." to the correct db name. Notice that the "DROP USER..." is commented out. Be sure to test it first before uncommenting the "DROP" to make sure the users are the ones you want.
Hope it helps,
Hank
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply