Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) or accounts that aren't linked (an account may have been deleted at the server level but not db level, then readded but the db level was never cleaned up).

I've got a very messy server and it would be awesome if there was a query to run to find these.

share|improve this question
2  

2 Answers 2

up vote 5 down vote accepted

Apologies to the creator of this script for not giving credit but I've no idea where I sourced it from. It's been in the toolbox for a while.

This iterates through all databases and lists the orphaned users by database, along with the drop command to remove them. There may be a neater/newer way of handling this but this appears to function correctly on 2005-2012.

DECLARE @SQL nvarchar(2000)
DECLARE @name nvarchar(128)
DECLARE @database_id int

SET NOCOUNT ON;

IF NOT EXISTS 
    (SELECT name FROM tempdb.sys.tables WHERE name like '%#orphan_users%')
BEGIN
    CREATE TABLE #orphan_users
        (
        database_name nvarchar(128) NOT NULL,
        [user_name] nvarchar(128) NOT NULL,
        drop_command_text nvarchar(200) NOT NULL
        )
END

CREATE TABLE #databases 
(
    database_id int NOT NULL
    , database_name nvarchar(128) NOT NULL
    , processed bit NOT NULL
)

INSERT
    #databases 
    ( database_id
    , database_name
    , processed )
SELECT 
    database_id
    , name
    , 0 
FROM 
    master.sys.databases 
WHERE 
    name NOT IN 
    ('master'
    , 'tempdb'
    , 'msdb'
    , 'distribution'
    , 'model')

WHILE (SELECT COUNT(processed) FROM #databases WHERE processed = 0) > 0
BEGIN
    SELECT TOP 1
        @name = database_name,
        @database_id = database_id
    FROM #databases
    WHERE processed = 0
    ORDER BY database_id

    SELECT @SQL =

'USE [' + @name + '];
INSERT INTO #orphan_users (database_name, user_name, drop_command_text)
SELECT 
    DB_NAME()
    , u.name
    , ' + '''' 
    + 'USE [' + @name + ']; ' 
    + 'DROP USER [' 
    + '''' + ' + u.name 
    + ' + '''' + '] ' 
    + '''' + '
FROM
    master..syslogins l
RIGHT JOIN 
    sysusers u 
ON l.sid = u.sid
WHERE   
    l.sid IS NULL
AND issqlrole <> 1
AND isapprole <> 1
AND ( u.name <> ' + '''' + 'INFORMATION_SCHEMA' + ''''
        + ' AND u.name <> ' + '''' + 'guest' + ''''
        + ' AND u.name <> ' + '''' + 'dbo' + ''''
        + ' AND u.name <> ' + '''' + 'sys' + ''''
        + ' AND u.name <> ' + '''' + 'system_function_schema' + '''' + ')'

    PRINT @SQL;

    EXEC sys.sp_executesql @SQL

    UPDATE 
        #databases 
    SET 
        processed = 1 
    WHERE 
        database_id = @database_id;
END

SELECT 
    database_name
    , [user_name]
    , drop_command_text 
FROM 
    #orphan_users 
ORDER BY 
    [database_name]
    , [user_name];

DROP TABLE #databases;
DROP TABLE #orphan_users;

SET NOCOUNT OFF;
share|improve this answer

This sp_change_users_login is depreciated as of SQL 2008 but still works well. If you pass in the 'report' option then it will list all of the users that don't have an associated login.

EXEC sp_change_users_login 'report'

If you want to run it for all of your databases you can do it like this.

EXEC sp_msforeachdb 'use [?]; PRINT ''?''; EXEC sp_change_users_login ''report'';'

If you look it up in BOL you will also find options for fixing "orphaned" users.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.