Technical Article

usp_ShowOrphanUsers - SQL 2k

,

Check Orphaned logins ie, not associated with any database on the current instance.

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
--
-- usp_ShowOrphanUsers.sql - Check Orphaned SQL Logins
--
-- 2007-06-19 Pedro Lopes (Novabase) pedro.lopes@novabase.pt
-- EXEC usp_ShowOrphanUsers
--

BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS, 
[Orphaned User] sysname COLLATE Latin1_General_CI_AS,
[Type User] sysname COLLATE Latin1_General_CI_AS
)
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', 'pubs', 'northwind')
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 <> ''guest'' and su.name <> ''information_schema'' and su.name <> ''system_function_schema''
AND NOT EXISTS (SELECT 1 FROM master..sysxlogins sl WHERE su.sid = sl.sid)'
INSERT INTO #Results EXEC (@Qry)
END
SELECT * FROM #Results ORDER BY [Database Name], [Orphaned User]
IF @@ROWCOUNT = 0
PRINT 'No orphaned users exist in this server.'
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating