text: /*+---------------------------------------------------------------------------
  | Purpose:	To check for non-service accounts
  | Note:		SQLCmdMode Script --> on the SSMS menu bar "Query" | "SQLCMD Mode"
  +-----------------------------------------------------------------------------
  */
  
  :setvar _server "YourServerName" -- change the server here
  :setvar _database "master"
  :connect $(_server)
  
  USE [$(_database)];
  GO
  
  :setvar login_name "" -- to search for an individual account enter it here e.g. "Domain\UserName"
  :setvar ssrs_service_account "Domain\ssrs_service_account"
  
  SET XACT_ABORT ON
  BEGIN TRANSACTION;
  
  PRINT '=====================================================================';
  PRINT 'define all services accounts ... ';
  
  SELECT tbl.* INTO #service_accounts FROM (VALUES
  	  ('##MS_PolicyEventProcessingLogin##')
  	, ('##MS_PolicyTsqlExecutionLogin##')
  	, ('NT AUTHORITY\SYSTEM')
  	, ('NT Service\MSSQLSERVER')
  	, ('NT SERVICE\SQLSERVERAGENT')
  	, ('NT SERVICE\SQLTELEMETRY')
  	, ('NT SERVICE\SQLWriter')
  	, ('NT SERVICE\Winmgmt')
  	, ('public')
  	, ('sa')
  ) tbl ([Login_Name]) 
  
  PRINT '=====================================================================';
  PRINT 'check databases owners ... ';
  
  	SELECT 
  		  [Server_Name] = @@SERVERNAME
  		, [Database_Name] = db.[name]
  		, [Database_Owner] = sl.[Name]
  		, [CommandToRun] = (CASE WHEN db.[is_read_only] = 1 THEN '-- Remove ReadOnly State' WHEN db.[state_desc] = 'ONLINE' THEN 'ALTER AUTHORIZATION on DATABASE::[' + db.[name] + '] to [sa];' ELSE '-- Turn On ' END)
  		--, [Database_ID] = db.[database_id]
  		--, [Current_State] = db.[state_desc]
  		--, [Read_Only] = db.[is_read_only]
  	FROM 
  		[master].[sys].[databases] AS db
  		INNER JOIN [master].[sys].[syslogins] AS sl ON db.[owner_sid] = sl.[sid]
  	WHERE 
  		1=1
  		AND sl.[Name] NOT IN(SELECT [Login_Name] FROM #service_accounts)
  		AND (sl.[Name] = N'$(login_name)' OR N'$(login_name)' = N'')
  	ORDER BY 
  		db.[Name]
  
  PRINT '=====================================================================';
  PRINT 'check databases users ... ';
  
  	DECLARE @dbs_users TABLE 
  		(
  		  [Database_Name] SYSNAME
  		, [User_Name] SYSNAME
  		, [Login_Type] SYSNAME
  		, [Associated_Role] VARCHAR(MAX)
  		, [Create_Date] DATETIME
  		, [Modify_Date] DATETIME
  		)
  
  	INSERT @dbs_users
  	EXEC sp_MSforeachdb '
  	USE [?]
  	SELECT 
  		  [Database_Name] = ''?''
  		, [User_Name] = CASE dp.[name] WHEN ''dbo'' THEN (SELECT SUSER_SNAME([owner_sid]) FROM [master].[sys].[databases] WHERE [name] =''?'') ELSE dp.[name] END
  		, [Login_Type] = dp.[type_desc]
  		, [Associated_Role] = isnull(USER_NAME(dm.role_principal_id),'''')
  		, dp.[create_date]
  		, dp.[modify_date]
  	FROM 
  		[sys].[database_principals] AS dp
  		LEFT JOIN [sys].[database_role_members] AS dm ON dp.[principal_id] = dm.[member_principal_id]
  	WHERE 
  		1=1
  		AND dp.[sid] IS NOT NULL 
  		AND dp.[sid] NOT IN (0x00) 
  		AND dp.[is_fixed_role] != 1 
  		AND dp.[name] NOT LIKE ''##%'''
  
  	SELECT 
  		  [Server_Name] = @@SERVERNAME
  		, [Database_Name]
  		, [User_Name]
  		, [Create_Date]
  		, [Modify_Date]
  		, [Permissions_User] = STUFF((
  				SELECT ',' + CONVERT(VARCHAR(500), [Associated_Role])
  				FROM @dbs_users AS dbu2
  				WHERE dbu1.[Database_Name] = dbu2.[Database_Name]
  					AND dbu1.[User_Name] = dbu2.[User_Name]
  				FOR XML PATH('')
  				), 1, 1, '')
  		, [CommandToRun] = 'USE [' + [Database_Name] + ']; DROP USER [' + [User_Name] + '];'
  		--, [Login_Type]
  	FROM 
  		@dbs_users AS dbu1
  	WHERE 
  		1=1
  		AND [User_Name] NOT IN(SELECT [Login_Name] FROM #service_accounts)
  		AND [Login_Type] = 'WINDOWS_USER'
  		AND ([User_Name] = N'$(login_name)' OR N'$(login_name)' = N'')
  	GROUP BY 
  		  [Database_Name]
  		, [User_Name]
  		, [Create_Date]
  		, [Modify_Date]
  		--, [Login_Type]
  	ORDER BY 
  		  [Database_Name]
  		, [User_Name]
  
  PRINT '=====================================================================';
  PRINT 'check agent jobs ... ';
  
  	SELECT 
  		  [Server_Name] = @@SERVERNAME
  		, [SQL_Agent_Job_Name] = sj.[name]
  		, [Job_Owner] = sl.[name]
  		, [CommandToRun] = 'EXEC [msdb].[dbo].[sp_update_job] @job_id=N''' + CAST(sj.[job_id] AS VARCHAR(150)) + ''', @owner_login_name=N''sa'' '
  		--, sj.[description]
  		--, sc.[name]
  	FROM 
  		[msdb].[dbo].[sysjobs] AS sj
  		INNER JOIN [master].[sys].[syslogins] AS sl ON sj.[owner_sid] = sl.[sid]
  		INNER JOIN [msdb].[dbo].[syscategories] AS sc ON sc.[category_id] = sj.[category_id]
  	WHERE 
  		1=1
  		AND sl.[Name] NOT IN(SELECT [Login_Name] FROM #service_accounts)
  		AND (sl.[name] = N'$(login_name)' OR N'$(login_name)' = N'')
  	ORDER BY 
  		sj.[name]
  
  PRINT '=====================================================================';
  PRINT 'check report subscriptions ... ';
  
  	IF DB_ID('ReportServer') IS NOT NULL
  		WITH 
  		service_account
  		AS
  		(
  			SELECT [service_account_id] = [UserID], [UserName] FROM [ReportServer].[dbo].[Users] WHERE [UserName] =  N'$(ssrs_service_account)'
  		)
  		SELECT DISTINCT
  			  [Server_Name] = @@SERVERNAME
  			, [Report_Name] = rp.[Name]
  			, [Subscription_Owner] = ou.[UserName]
  			, [Subscription_Owner_ID] = ou.[UserID]
  			, [CommandToRun] = 'UPDATE [ReportServer].[dbo].[Subscriptions] SET [OwnerID] = ''' + CAST((SELECT [service_account_id] FROM service_account) AS VARCHAR(MAX)) + ''' WHERE [OwnerID] = ''' + CAST(ou.[UserID] AS VARCHAR(MAX)) + ''''
  			--, sb.[Report_OID]
  		FROM 
  			[ReportServer].[dbo].[Subscriptions] AS sb
  			INNER JOIN [ReportServer].[dbo].[Users] AS ou ON ou.[UserID] = sb.[OwnerID]
  			INNER JOIN [ReportServer].[dbo].[Catalog] AS rp ON rp.[ItemID] = sb.[Report_OID]
  		WHERE 
  			1=1
  			AND ou.[UserName] NOT IN(SELECT [Login_Name] COLLATE Latin1_General_CI_AS FROM #service_accounts)
  			AND (ou.[UserName] = N'$(login_name)' OR N'$(login_name)' = N'')
  
  
  PRINT '******* ROLLBACK TRANSACTION ******* ';
  ROLLBACK TRANSACTION;
  
  --PRINT '******* COMMIT TRANSACTION ******* ';
  --COMMIT TRANSACTION;
  