Wednesday 20 April 2011

ASE : How to find out the orphaned users

As we refresh UAT server with production backup, it's possible that some orphaned users are left on UAT server. Security auditor treated each orphaned user as 1 count of Medium level vulnerability finding. So it's important to delete orphaned users from servers.

Here is the sql I used to find out orphaned users

select * from userDbName1..sysusers where suid not in (select suid from master..syslogins) and suid > 0
go
select * from userDbName2..sysusers where suid not in (select suid from master..syslogins) and suid > 0
go

1 comment: