Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Rocks!

SQL Database Administrator/Developer. Background in developing OLTP/document based databases, SQLXML and performance tuning with an unhealthy fascination for the SQL query optimiser!

Finding orphaned database users

Database users can become orphaned for a few reasons. For example, a database restore to another instance or deleting the corresponding SQL login would have the effect of leaving a database user orphaned. Development/testing SQL servers may also experience this due to the number of ad-hoc updates that may occur over the years. If you are unlucky enough your production servers may even suffer from this.

SQL provides a procedure that will provide this information on a database level:

EXEC sp_change_users_login @Action='Report'

This is great and works well, however if you want to report across all of your databases, you'll need to run this against each database in turn.

So here is a script that I sometimes use to search all non-system databases which aims to find any database users that have been orphaned or do not have a corresponding SQL server login. Armed with this information, you are then in a position to deal with any that the script finds. e.g. delete the user or link it using sp_change_users_login.

DECLARE cur CURSOR FAST_FORWARD FOR 
SELECT name FROM sys.databases 
WHERE database_id > 4 

OPEN cur  

DECLARE @SQL NVARCHAR(MAX), @DBName SYSNAME 
DECLARE @Results TABLE (DBName SYSNAME, UserName SYSNAME, UserSID VARBINARY(MAX))  

FETCH NEXT FROM cur into @DBName  

WHILE @@FETCH_STATUS = 0 
BEGIN 
	SET @SQL = 'USE ' + @DBName + ';SELECT ''' + @DBName + ''' AS DBName, 
			UserName = name, UserSID = sid from sysusers 
			WHERE issqluser = 1 AND 
				(sid IS NOT NULL AND sid <> 0x0) AND 
				(LEN(sid) <= 16) AND SUSER_SNAME(sid) IS NULL'     

	INSERT INTO @Results 
	EXEC(@SQL)  

	FETCH NEXT FROM cur into @DBName  
END  

CLOSE cur 
DEALLOCATE cur  

SELECT * FROM @Results

Comments

Posted by Anonymous on 21 October 2011

Pingback from  Dew Drop &ndash; October 21, 2011 | Alvin Ashcraft&#039;s Morning Dew

Posted by Mike Good on 23 October 2011

Nice.  You did not explain but I'm pretty sure the LEN(sid) check is there to eliminate DB users who are not orphans, but otherwise appear to be because they were created WITHOUT LOGIN. As far as I know, this continues to be an  undocumented workaround. Ref

<a href="www.sqlservercentral.com/.../a>

Posted by Vladimir Mednikov-324895 on 24 October 2011

Nice script, thank you. I think it would be a good idea to present  script that fixes orphan users (only in the current database, sorry):

Declare @ExecStr varchar(256)

Declare @UserName varchar(256)

Create table #Orphanage(UserName varchar(256))

insert #Orphanage(UserName)

select UserName = name  from sysusers

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

and suser_sname(sid) is null

DECLARE Adopt_them CURSOR

FOR select UserName from #Orphanage

OPEN Adopt_them

FETCH NEXT FROM Adopt_them into @UserName

WHILE @@FETCH_STATUS = 0

begin

 set @ExecStr='EXEC sp_change_users_login ''UPDATE_ONE'', ''' + @UserName +''',''' + @UserName +''''

 execute(@ExecStr)

 print @UserName + '  Updated'

 FETCH NEXT FROM Adopt_them into @UserName

end

CLOSE Adopt_them

DEALLOCATE Adopt_them

Posted by Arthur Olcot on 24 October 2011

Hi Mike, Thanks for the link to the forum, I wasn't aware of that and it was interesting reading. I got much of the "where" clause from looking at the procedure sp_change_users_login which included the LEN(sid).

Posted by gvarol on 28 October 2011

I like your idea of looping through databases. This may be an overkill but I created a system procedure to loop through databases, I have a hard time organizing numerous scripts over here, but we use sqlprompt intellisense that lists me the system databases in a hard beat. The procedure reports the invalid users and also fills the results into a global temporary table to be queried.

USE MASTER

GO

IF OBJECT_ID('dbo.sp_FindInvalidLogins') IS NULL

   EXEC ('CREATE PROCEDURE dbo.sp_FindInvalidLogins AS SELECT 1 AS ID')

GO

ALTER PROCEDURE dbo.sp_FindInvalidLogins

   @SQL VARCHAR(MAX) = NULL OUTPUT,

   @PrintSQL BIT = 0,

   @ExecuteSQL BIT = 1

AS

SET NOCOUNT ON

SET @SQL = 'SET NOCOUNT ON

IF OBJECT_ID(''TEMPDB..##sp_change_users_login'') IS NOT NULL DROP TABLE ##sp_change_users_login

CREATE TABLE ##sp_change_users_login(DatabaseName varchar(128) NOT NULL, UserName sysname, UserSID varbinary(85))

DECLARE @Result TABLE(UserName sysname, UserSID varbinary(85))

'

SELECT  @SQL = @SQL + 'USE [' + d.name + ']

DELETE @Result

INSERT @Result

EXEC sp_change_users_login @Action=''Report''

INSERT ##sp_change_users_login (DatabaseName, UserName, UserSID)

SELECT ''' + d.NAME + ''' as DatabaseName, UserName, UserSID

FROM @Result

'

FROM    sys.databases d (NOLOCK)

WHERE   STATE = 0

       AND NOT EXISTS ( SELECT *

                        FROM   sys.dm_tran_locks t1 (NOLOCK)

                        WHERE  resource_type = 'database'

                               AND request_status = 'grant'

                               AND request_mode = 'u'

                               AND t1.resource_database_id = d.database_id )  

SELECT  @SQL = @SQL + '

SELECT DatabaseName, UserName, UserSID

FROM ##sp_change_users_login'

IF @PrintSQL = 1

   PRINT @SQL

ELSE

   PRINT 'SELECT DatabaseName, UserName, UserSID

FROM ##sp_change_users_login

GO'

IF @ExecuteSQL = 1

   EXEC(@SQL)

GO

EXEC sys.sp_MS_marksystemobject

   sp_FindInvalidLogins

GO

Posted by Anonymous on 28 October 2011

Pingback from  Finding orphaned database users | SQL Server | Syngu

Posted by Arthur Olcot on 29 October 2011

Hi gvarol. I like your where clause against sys.databases, especially the state column. I overlooked the scenario of databases not being online and will add that my script. Thanks!

Posted by allan_leake on 9 November 2011

Very nice script. However it failed when I was attempting to scan an instance that had SharePoint databases that used an auto-generated name containing a dash (or hyphen). I changed the assignement to include square brackets around the database name and it worked fine.

e.g.

SET @SQL = 'USE [' + @DBName + '];SELECT ''' + @DBName + ''' AS DBName, ......

Cheers!

Posted by Arthur Olcot on 9 November 2011

Hi allan_leake. Yes an oversight on my part there. Thanks for the update.

Leave a Comment

Please register or log in to leave a comment.