Need a list of orphaned users - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need a list of orphaned users

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-03-26 : 16:53:25

Hi,

I need to get a list of all orphaned users on a server that has about 100 databases. I looked up the functions needed for what I want but I can't everything work together. My goal is to produce a result set that looks something like this:

db_name.....UserName......userSID
ADMIN.......Joe_Blow......123456789...
ADMIN.......Sheila_Blow...123456788...
DebtCalc....Joe_Blow......123456787...
......

And so on. I am trying to create a temp table and select the db_name(), and make use of sp_change_user_login @action='Report' to get the data. I thought I could loop through all the databases in sys.databases and make it happen. I know the individual ideas will work but I'm having a heck of a time putting them all together. Here is the query I started with:


create table #orphaned_users_by_db
(dbname nvarchar(50),
username nvarchar(50),
usersid nvarchar(50))

insert into #orphaned_users_by_db
select db_name(), exec sp_change_users_login @Action='Report'

drop table #orphaned_users_by_db

I havn't started the loop yet to make this run on all databases.

Help!

Craig Greenwood

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-03-27 : 00:40:33

The following query can be run in each database ( i have not tested it )
USE <<database_name>> -- Execute for each database
GO

SELECT a.name AS OrphanUserName, a.type_desc AS UserType
FROM sys.database_principals a
LEFT OUTER JOIN sys.server_principals b
ON a.sid = b.sid
WHERE b.sid IS NULL
AND a.type In ('S', 'U', 'G')
AND a.name NOT in ('sys', 'INFORMATION_SCHEMA', 'guest')

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

craigwg
Posting Yak Master

154 Posts

Posted - 2010-03-30 : 13:12:20

Thanks,

I used your query and wrote it as a cursor. This involved writing two stored procedures. I wanted to post my result here for anyone else doing research and I have a few follow up questions.

This is the first of two stored procedures. It is called "FindOrphanedUsers". It accepts one argument: the name of the single database you need orphaned users for:


USE [ADMIN]
GO
/****** Object:  StoredProcedure [dbo].[FindOrphanedUsers]    Script Date: 03/30/2010 11:05:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[FindOrphanedUsers]
@databasename sysname
as
set nocount on

Create table #OrphUsers
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

declare @sql varchar(1000)
set @sql = '
	use '+@databasename+'
	insert into #OrphUsers
	(DB, OrphanedUserName, UserType)
	SELECT db_name() as ''DB'', a.name AS OrphanUserName, a.type_desc AS UserType
	FROM sys.database_principals a
	LEFT OUTER JOIN sys.server_principals b
	ON a.sid = b.sid
	WHERE b.sid IS NULL
	AND a.type In (''S'', ''U'', ''G'')
	AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')

select * from #orphusers
'

exec (@sql)

The second stored procedure uses a cursor to run the first stored procedure against all databases. Finally the results of all queries are loaded into a temp table and displayed:

[code]
USE [ADMIN]
GO
/****** Object: StoredProcedure [dbo].[FindAllOrphanedUsers] Script Date: 03/30/2010 11:09:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[FindAllOrphanedUsers]
AS
Create table #OrphUsers
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

DECLARE @databasename sysname
DECLARE DatabaseLoop CURSOR FOR
(select name from sys.databases where state=0)
OPEN DatabaseLoop
FETCH NEXT FROM DatabaseLoop into @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #OrphUsers
exec FindOrphanedUsers @databasename
FETCH NEXT FROM DatabaseLoop into @databasename
END
CLOSE DatabaseLoop
DEALLOCATE DatabaseLoop

Select * from #orphusers order by db
[\code]

My first question is, as I run this I find a lot of users named dbo. To verify I looked at the users in the database and of course found the dbo user (I've always understood it to be a default). But sure enough, there is no entry called dbo in any of the logins on the server. So is this truly an orphaned user that can be deleted or should I just filter out users named "dbo" in my stored procedure?

Next question: Is there a more efficient way to do this?

Craig Greenwood

johnsemi
Starting Member

1 Post

Posted - 2012-08-15 : 13:17:58

This is a pretty old thread, but I expanded on (collapsed..?) craigwg's procedures with this (believe it does the same thing):


Create table #OrphUsers
(DatabaseName nvarchar(50),
OrphanedUserName nvarchar(100),
UserType nvarchar(50));

insert into #OrphUsers EXEC sp_MSforeachdb 'SELECT ''?'' as DatabaseName, a.name AS OrphanUserName, a.type_desc AS UserType
	FROM  [ ? ].sys.database_principals a
	LEFT OUTER JOIN [ ? ].sys.server_principals b
	ON a.sid = b.sid
	WHERE b.sid IS NULL
	AND a.type In (''S'', ''U'', ''G'')
	AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')'

select * from #OrphUsers 

drop table #OrphUsers

****NOTE: take the spaces out between the characters [ ? ] - I had to do that, otherwise it showed up as this dumb icon:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources