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