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.
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......userSIDADMIN.......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_dbselect 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 UserTypeFROM sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sidWHERE b.sid IS NULLAND 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE Proc [dbo].[FindOrphanedUsers]@databasename sysnameasset nocount onCreate 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 DatabaseLoopSelect * 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: |
|
|
|
|
|
|
|