Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

4.67 (3)

You rated this post out of 5. Change rating