Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Orphaned logins

Author  Topic 

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-01-28 : 17:10:08
How to find all orphaned logins in an instance. Logins that have not corrponding users in databases or don't have access to any database?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-28 : 17:46:40
Might be better ways, but this will do it:
Declare @t TABLE (usr sysname)
Declare @db sysname

Declare c Cursor
Read_Only
FOR
SELECT name FROM sys.databases where state = 0

Open c
Fetch Next FROM c INTO @db
WHILE @@fetch_status = 0
BEGIN
INSERT @t
SELECT s.name
from sys.database_principals d
join sys.server_principals s
on d.sid = s.sid
WHERE NOT EXISTS (SELECT usr FROM @t WHERE usr = s.name)

Fetch Next FROM c INTO @db
END
CLOSE c
DEALLOCATE c

SELECT p.name
FROM sys.database_principals p
LEFT JOIN
@t t
On t.usr = p.name
WHERE t.usr IS NULL
And p.principal_id > 5
And p.type in ('U', 'S')
Go to Top of Page
   

- Advertisement -