Close Menu

SQL-SERVER

 


Script to Script to find orphaned users
using T-SQL Script


The process we would be following here is a two step procedure

Step 1:

Create a store procedure usp_ShowOrphanUsers
This procedure helps us in revealing those orphaned Users to be to reveal if at all there exists any orphaned users. The advantage of this methos is because this procedure can be reused many times.

Step 2:

Execute the stored procedure

Before You Proceed We recommend you to read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.



STEP:1


 

-- Sript to findout Orphaned Users
-- This procedure consists of two steps
-- Step 1 :- Create a Stored procedure named usp_ShowOrphanUsers
-- Step 2 :- Execute the Stored Procedure to reveal


IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_ShowOrphanUsers]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[usp_ShowOrphanUsers]

GO

CREATE PROC dbo.usp_ShowOrphanUsers

AS

BEGIN

CREATE TABLE #Results

([Database Name] sysname ,

[Orphaned User] sysname ,

[Type User] sysname )

SET NOCOUNT ON 

DECLARE @DBName sysname, @Qry NVARCHAR(4000)

SET @Qry = ''

SET @DBName = ''

WHILE @DBName IS NOT NULL

BEGIN

SET @DBName =

(

SELECT MIN(name)

FROM master..sysdatabases

WHERE name NOT IN

('master', 'model', 'tempdb', 'msdb', 'distribution', 'pubs', 'northwind', 'dba_database')

AND DATABASEPROPERTY(name, 'IsOffline') = 0

AND DATABASEPROPERTY(name, 'IsSuspect') = 0

AND name > @DBName

)

IF @DBName IS NULL BREAK

SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],

CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],

[Type User] =

CASE isntuser

WHEN ''0'' THEN ''SQL User''

WHEN ''1'' THEN ''NT User''

ELSE ''Not Available''

END

FROM ' + QUOTENAME(@DBName) + '..sysusers su

WHERE su.islogin = 1

AND su.name NOT IN (''INFORMATION_SCHEMA'', ''sys'', ''guest'', ''dbo'', ''system_function_schema'')

AND NOT EXISTS (SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid)'

INSERT INTO #Results

EXEC master..sp_executesql @Qry

 

END

SELECT *

FROM #Results

ORDER BY [Database Name], [Orphaned User]

IF @@ROWCOUNT = 0

PRINT 'No orphaned users exist in this server.'

END

GO

 
--STEP 2:-

--Execute the proc usp_ShowOrphanUsers

--EXEC [dbo].[usp_ShowOrphanUsers]


STEP:2


EXEC [dbo].[usp_ShowOrphanUsers]
you can download the Script here here