Find Orphan Logins in SQL Server2000

,

Hi,

exec sp_change_users_login 'report' displays the list of orphan USERS in a database but we don't have any builtin SP which displays the orphan LOGINS.
By Orphan Logins (not users), I mean the Logins that don't have any access to any databases and are sitting idle on the server (also aren't member of any fixed server role).
We can look "AUSER" field of sp_helplogins but again this procedure displays some unwanted information also and moreover, this does not take into account if the login is a member of any fixed server role.

My script usp_OrphanLogins.sql takes care of all the abovementioned concerns.

Also, this SP gives a user friendly message and quits if any of the database is not accessible. This makes sense because we may have some databse restore in progress or some database offline at times.

I'm creating the SP in tempdb database and to exec it simply run EXEC tempdb..usp_OrphanLogins.

For any queries, mail me at a_madhur@rediffmail.com

Thanks!
Madhur

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_OrphanLogins]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_OrphanLogins]
GO
USE tempdb
GO
CREATE PROCEDURE usp_OrphanLogins
AS
SET NOCOUNT ON

--**********************************************************************************
-- 1. File Name: usp_OrphanLogins.sql
-- 2. Description: Displays The Logins not mapped to any database and 
--		   not a member of any fixed server role
-- 3. Usage: EXEC usp_OrphanLogins
-- 4. Modification Log    
-- Ver.No.	Date		Author		Modification     
-- 0.00		06/20/2007   	Madhur Agarwal 	Initial Version  
-- 0.00		06/21/2007   	Madhur Agarwal 	Taken care of fixed server roles
-- 0.00		06/21/2007   	Madhur Agarwal 	Taken care of offline/suspect databases
--**********************************************************************************

DECLARE	 @query nvarchar(3550)
	,@DBName sysname

SET @DBName = ''

--create work tables
CREATE TABLE #tb2_logins (LoginName sysname NOT Null, IsOrphan char(5)Null)
CREATE TABLE #tb1_users  (LoginName sysname NOT Null, DBName sysname NOT Null)

--Only SA can run this
If is_srvrolemember('sysadmin') = 0
	BEGIN
		PRINT 'only SYSADMIN can EXEC the utility'
		RETURN 
	END

--Loop thru Databases
WHILE @DBName IS NOT NULL
BEGIN   --Loop A
	SET @DBName = ( 
			SELECT MIN(name) 
			FROM master..sysdatabases 
			WHERE name NOT IN 
				('model','distribution', 'pubs', 'northwind')
				AND name > @DBName
			)
	If (SELECT DATABASEPROPERTYEX(@DBName, 'status'))<>'ONLINE'
	BEGIN
		PRINT 'Error::'
		PRINT 'The Database '+@DBName+' is not ONLINE. Exiting from the SP. Please try again later'
		RETURN
	END
	ELSE --If
	BEGIN
		IF @DBName IS NULL BREAK

		--Add the User info to work table
		SELECT @query = '
			INSERT #tb1_users (DBName, LoginName)
			SELECT N' + quotename(@DBName, '''') + ',l.loginname
	      		FROM ' + quotename(@DBName, '[') + '.dbo.sysusers u, master.dbo.syslogins l
		        WHERE u.sid  = l.sid' 

		EXEC(@query)
	END --Else	
END --Loop A

--Checkpoint
--select * from #tb1_users

--Populate Logins Work Table
INSERT #tb2_logins (LoginName ,IsOrphan) SELECT loginname, 'Yes' FROM master.dbo.syslogins

-- Update Login Status
UPDATE #tb2_logins
	SET IsOrphan = 'No' 
	FROM #tb2_logins tl, master.dbo.syslogins l, #tb1_users tu
	WHERE 	
		(tl.LoginName = l.LoginName and 
						(l.sysadmin = 1 
						or l.securityadmin = 1 
						or l.serveradmin = 1 
						or l.setupadmin = 1
						or l.processadmin = 1
						or l.diskadmin = 1
						or l.dbcreator = 1
						or l.bulkadmin = 1
						))
		or tl.LoginName = tu.LoginName

--Display Results
SELECT * FROM #tb2_logins WHERE IsOrphan = 'Yes' ORDER BY loginname
GO

Rate

5 (1)

Share

Share

Rate

5 (1)