Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List orphaned users from all databases Expand / Collapse
Author
Message
Posted Thursday, December 10, 2009 1:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, December 29, 2009 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:30 AM
Points: 3, Visits: 135
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
Posted Tuesday, January 5, 2010 2:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, January 5, 2010 9:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:59 PM
Points: 2,213, Visits: 266
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
Posted Tuesday, March 23, 2010 7:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, May 13, 2013 12:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1452264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse