Please, Sir, I want some more… orphaned database users.

Permissions on our development servers are considerably less stringent than elsewhere with developers afforded the freedom to restore backup’s ad-hoc. Occasionally (or more accurately, often) there are redundant credentials in the form of Database users not attached to any logins. This can be incredibly irritating.

I wanted to write something that would identify these orphaned users across the server, quickly and accurately, perhaps even have it scheduled for a periodic review.

The following script will interrogate the users in each database on the server. I select all records from sysusers where the record contains a SID and I then use suser_sname(SID), which returns the login name associated with a security identification number (SID). If the returned value is null, the database user is not attached to a valid login.

I then applied various filters to exclude records I do not want to see eg database and application roles.

A temporary table is then populated with data from each database using the sp_MSforeachdb procedure.

/*************************************************

** Purpose: To retun database users (for each db) orphaned from any login.

** Created By: James Howard

** Created On: 03 DEC 09

*************************************************/

--create a temp table to store the results

CREATE TABLE #temp (

DatabaseName NVARCHAR(50),

UserName NVARCHAR(50)

)

--create statement to run on each database

declare @sql nvarchar(500)

SET @sql='select ''?'' as DBName

, name AS UserName

from ?..sysusers

where (sid is not null and sid <> 0x0)

and suser_sname(sid) is null and

(issqlrole <> 1) AND

(isapprole <> 1) AND

(name <> ''INFORMATION_SCHEMA'') AND

(name <> ''guest'') AND

(name <> ''sys'') AND

(name <> ''dbo'') AND

(name <> ''system_function_schema'')

order by name

'

--insert the results from each database to temp table

INSERT INTO #temp

exec SP_MSforeachDB @sql

--return results

SELECT * FROM #temp

DROP TABLE #temp

I can now use this to schedule the retrieval of the results and will be able to immediately identify orphaned users.

Hopefully someone will find this useful. I’d be interested to hear from anyone who has a more efficient method to achieve the same results.