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 
In this article we will show some common roles and queries related to Azure SQL Data Warehouse.
2017-07-25
4,980 reads
With AD Authentication via groups, SQL Server is vulnerable to orphaned Windows users' logins being added to SQL Server at a later date. This article gives an improved user audit script that detects orphaned DB Users and also a delete script.
2016-07-18
2,884 reads
2013-10-31
2,191 reads
Many experienced DBAs understand the issues with matching up users and logins in a restored database. But what do you do when the database is read only? New author Tychang Chen brings us a technique that can help.
2009-12-28
13,289 reads
Permissions on our development servers are considerably less stringent than elsewhere with developers afforded the freedom to restore backup’s ad-hoc....
2009-12-04
2,270 reads