List orphaned users from all databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by James Howard » List orphaned users from all databases

List orphaned users from all databases

Rate Topic

Display Mode

Topic Options

Author

Message

Mr James Howard

Posted Thursday, December 10, 2009 1:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 1:13 AM
Points: 190, Visits: 9,356

Comments posted to this topic are about the item List orphaned users from all databases

James Howard

Post #832038

smokin_bob

Posted Tuesday, December 29, 2009 2:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:38 PM
Points: 3, Visits: 104

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 results
CREATE TABLE #temp (
DatabaseName NVARCHAR(50),
UserName NVARCHAR(50)
)

--create statement to run on each database
declare @sql nvarchar(500)
SET @sql='select ''?'' as DBName
, name AS UserName
from [?]..sysusers
where (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 table
INSERT INTO #temp
exec SP_MSforeachDB @sql
--return results
--SELECT * FROM #temp

SELECT '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 #temp

DROP TABLE #temp

----------------------------------------------------------------------------

Post #840071

Mr James Howard

Posted Tuesday, January 05, 2010 2:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 1:13 AM
Points: 190, Visits: 9,356

Good thinking Bob, thanks!

James Howard

Post #841924

vlad-548036

Posted Tuesday, January 05, 2010 9:43 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,801, Visits: 257

cute script, but fails with error:

"Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated."

there is also another much shorter script for this (which actually works):

EXEC sp_MSforeachdb @command1='use ?
select db_name();
exec ?.dbo.sp_change_users_login ''Report'';'

Post #842187

Madhu R

Posted Tuesday, March 23, 2010 7:12 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, March 24, 2013 6:56 PM
Points: 6, Visits: 42

Hi,

When I run this to find out the users... the results page are returned as error. which is below

Msg 2812, Level 16, State 62, Line 31
Could not find stored procedure 'SP_MSforeachDB'.

(0 row(s) affected)

Post #888621

opc.three

Posted Monday, May 13, 2013 12:16 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

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

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1452264

« Prev Topic | Next Topic »

Permissions