USE master
GO
IF OBJECT_ID('sp_FindInvalidLogins') IS NULL EXEC ('CREATE PROCEDURE sp_FindInvalidLogins AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_FindInvalidLogins
@SQL VARCHAR(MAX) = NULL OUTPUT,
@PrintSQL BIT = 0,
@ExecuteSQL BIT = 1
AS
SET NOCOUNT ON
SET @SQL = 'SET NOCOUNT ON
IF OBJECT_ID(''TEMPDB..##sp_change_users_login'') IS NOT NULL DROP TABLE ##sp_change_users_login
CREATE TABLE ##sp_change_users_login(DatabaseName varchar(128) NOT NULL, UserName sysname, UserSID varbinary(85))
DECLARE @Result TABLE(UserName sysname, UserSID varbinary(85))
'
SELECT @SQL = @SQL + 'USE [' + d.name + ']
DELETE @Result
INSERT @Result
EXEC sp_change_users_login @Action=''Report''
INSERT ##sp_change_users_login (DatabaseName, UserName, UserSID)
SELECT ''' + d.NAME + ''' as DatabaseName, UserName, UserSID
FROM @Result
'
FROM sys.databases d (NOLOCK)
WHERE STATE = 0
AND NOT EXISTS ( SELECT *
FROM sys.dm_tran_locks t1 (NOLOCK)
WHERE resource_type = 'database'
AND request_status = 'grant'
AND request_mode = 'u'
AND t1.resource_database_id = d.database_id )
SELECT @SQL = @SQL + '
SELECT DatabaseName, UserName, UserSID
FROM ##sp_change_users_login'
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
ELSE
PRINT 'SELECT DatabaseName, UserName, UserSID
FROM ##sp_change_users_login
GO'
IF @ExecuteSQL = 1
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject sp_FindInvalidLogins
GO