Orphaned login report for all databases

,

Automating redundant items are time savers. I got tired of running this against each db with a USE command or dropping down the db dropdown box. This is a simple cursor that loops through each db and will show any orphaned logins if they exist on the system.

Usage: Cut and paste into a query window and run

set nocount on
DECLARE @dbname varchar(255) 
DECLARE db_loop CURSOR
	READ_ONLY FOR
	Select [name] from master..sysdatabases
		where [name] not in ('tempdb') 
 OPEN db_loop

FETCH NEXT FROM db_loop INTO @dbname
	WHILE (@@fetch_status <> -1)
	BEGIN
 
 EXEC ('EXEC [' + @dbname + '].dbo.sp_change_users_login @Action=Report')

	FETCH NEXT FROM db_loop INTO @dbname
	END

CLOSE db_loop
DEALLOCATE db_loop
GO

Rate

4.67 (3)

Share

Share

Rate

4.67 (3)