SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by James Howard / List orphaned users from all databases / Latest Posts

RE: List orphaned users from all databases

opc.three — Mon, 13 May 2013 12:16:29 GMT

The script does not take into account Database Users that were created without a login, a.k.a. loginless users.

RE: List orphaned users from all databases

Madhu R — Tue, 23 Mar 2010 19:12:53 GMT

Hi, When I run this to find out the users... the results page are returned as error. which is belowMsg 2812, Level 16, State 62, Line 31Could not find stored procedure 'SP_MSforeachDB'. (0 row(s) affected)

RE: List orphaned users from all databases

vlad-548036 — Tue, 05 Jan 2010 09:43:15 GMT

cute script, but fails with error:"Msg 8152, Level 16, State 13, Line 1String or binary data would be truncated."there is also another much shorter script for this (which actually works):[quote]EXEC sp_MSforeachdb @command1='use ? select db_name();exec ?.dbo.sp_change_users_login ''Report'';'[/quote]

RE: List orphaned users from all databases

Mr James Howard — Tue, 05 Jan 2010 02:46:56 GMT

Good thinking Bob, thanks!

RE: List orphaned users from all databases

smokin_bob — Tue, 29 Dec 2009 14:23:36 GMT

So I expanded a little on your original script by creating the create and drop statements. The create statements are commented out but are there in case you delete a user that you really wanted./*************************************************** Purpose: To return database users (for each db) orphaned from any login.** Created By: James Howard** Created On: 03 DEC 09** Modified By: Bob Cole** Modified On: 29 Dec 09 - Added script to render create and drop statements.** The create statements are commented out but are there in case you delete a ** user that you really wanted.*************************************************/--create a temp table to store the resultsCREATE TABLE #temp (DatabaseName NVARCHAR(50),UserName NVARCHAR(50))--create statement to run on each databasedeclare @sql nvarchar(500)SET @sql='select ''?'' as DBName, name AS UserNamefrom [?]..sysuserswhere (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 tableINSERT INTO #tempexec SP_MSforeachDB @sql--return results--SELECT * FROM #tempSELECT 'USE ' + DatabaseName + CHAR(10)+ '--GO ' + CHAR(10)+ '--CREATE USER ' + UserName + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA=[' + UserName + ']' + CHAR(10)+ '--GO ' + CHAR(10)+ '' + CHAR(10)+ 'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + UserName + ''')' + CHAR(10)+ 'BEGIN' + CHAR(10)+ 'DROP SCHEMA [' + UserName + ']' + CHAR(10)+ 'DROP USER [' + UserName + ']' + CHAR(10)+ 'END'+ 'GO' + CHAR(10)+ '' + CHAR(10)+ '--------------------------------------------------------------------------------' + CHAR(10)+ '' + CHAR(10)FROM #tempDROP TABLE #temp----------------------------------------------------------------------------

List orphaned users from all databases

Mr James Howard — Thu, 10 Dec 2009 01:26:19 GMT

Comments posted to this topic are about the item [B]List orphaned users from all databases[/B]