Sunday, November 19, 2017
 
SQL Server Script Repository
Facebook Twitter LinkedIn Google+ Addthis
 
     

SQL Script - List Orphan Users Accounts

By Saleem Hakani on 8/24/2014

User accounts in SQL Server can become orphan whenever a new database is restored or attached. Orphan account means there is no associated login for that user and the when a user logs in to SQL Server using the account which has an orphan user in the database, login account will not be able to access database objects since the user in the database is orphan. Identifying and fixing orphan accounts from all user databases is important for any database administrator. Please note: After identifying an orphan user account, you will need to use SP_Change_Users_Login System Stored Procedure to either 'Auto_Fix','Report','Update_One'. (Example: EXEC sp_change_users_login 'Update_One', 'sqlcommunity', 'sqlcommunity');

Here's the script you can use to identify orphan accounts from all databases:

--Author: Saleem Hakani (http://sqlcommunity.com)

Declare @ORPHANUSER TABLE

(

SERVERNAME VARCHAR(100),

DBNAME VARCHAR(100),

ORPHAN_USERNAME VARCHAR(100),

CREATEDATE VARCHAR(100),

USERTYPE VARCHAR(100)

)

Insert @ORPHANUSER

EXEC SP_MSFOREACHDB 'USE [?]

SELECT @@ServerName as ServerName, DB_NAME() as DBNAME,

NAME as User_Name,

CREATEDATE as Date_Created,

(CASE

WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN''

WHEN ISNTGROUP = 1 THEN ''NT GROUP''

WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN''

END) [LOGIN TYPE] FROM sys.sysusers

WHERE SID IS NOT NULL

AND SID <> 0X0

AND ISLOGIN =1

AND SID NOT IN

(

SELECT

SID

FROM sys.syslogins

)'

SELECT * FROM @ORPHANUSER

Orphan SQL Accounts
Orphan Windows Accounts
SP_Change_User_Login
Minimize
Script Categories
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM