Identifying Orphan and mismatched userids
-
Hi,
Would someone know if there is a stored procedure to report on orphaned and mismatched userids for each database on a given server.
Thaks for the help.
Charles L. Nichols
Charles L. Nichols -
Take a look at sp_change_users_login using the report option.
USE MyDatabase
GO
EXEC sp_change_users_login @Action = 'Report'
GOIt'll report any orphaned users for the given database. There are some example scripts here on the site that deal with issuing a command across all databases, so match the two up and you should be set!
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
Here's a query I came up with:
exec sp_MSforeachdb 'select ''?'' as DB, u.name as Orphan
from
.dbo.sysusers u where u.islogin = 1 and u.sid > 0x01and not exists (select null from master.dbo.syslogins
where sid = u.sid and ((isntname = 0 and name = u.name)
or (isntname = 1 and right(name,len(u.name)) = u.name)))'
Since I usually don't want to just list the orphan names, I want to get rid of them, I use this query to generate the necessary commands:
exec sp_MSforeachdb 'select ''use
exec sp_dropuser ''' + u.name + '''from
.dbo.sysusers u where u.islogin = 1 and u.sid > 0x01and not exists (select null from master.dbo.syslogins
where sid = u.sid and ((isntname = 0 and name = u.name)
or (isntname = 1 and right(name,len(u.name)) = u.name)))'
-
I didn't realize parts of the query would be turned into emoticons.
The big green question mark icons (
) in the above queries should be a question mark inside square brackets.
.dbo.sysusers u where u.islogin = 1 and u.sid > 0x01Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply