CREATE TABLE #InvalidUsers( [SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ) INSERT INTO #InvalidUsers ( [SID] ,LOGIN_NAME ) EXEC sys.sp_validatelogins
- Whether the user owns a schema in the database (if so we need to change the schema ownership to dbo)
- Whether the user owns a the database. If so we need to change db ownership to a different user. In this example I will be changing the ownership to "sa"
We will insert the details into another temporary table. I will explain part by part, but once the script is completed you can execute it as a whole. I have provided the completed script at the end.
We will use another system stored procedure to execute a query in all the databases in the SQL Server instance (sys.sp_MSforeachdb)
DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT LOGIN_NAME FROM #InvalidUsers OPEN cur FETCH NEXT FROM cur INTO @LoginName WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = CONCAT( 'USE ?;INSERT INTO #InvalidUserDbInfo SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema FROM sys.database_principals AS DP LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id WHERE [type] = ''U'' and DP.name = ''',@LoginName,'''' ) EXEC sys.sp_MSforeachdb @Sql FETCH NEXT FROM cur INTO @LoginName END CLOSE cur DEALLOCATE cur
DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) ,@DBName AS NVARCHAR(128) ,@DbNameCurrent AS NVARCHAR(128) = '' ,@SqlSyntax AS NVARCHAR(MAX) = '' ,@OwnedSchema AS VARCHAR(128) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT IUDI.DBNAME ,IUDI.LOGIN_NAME ,IUDI.OWNED_SCH FROM #InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME OPEN cur FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema WHILE @@FETCH_STATUS = 0 BEGIN IF @DbNameCurrent <> @DBName BEGIN IF @DbNameCurrent <> '' SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13)) SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13)) SET @DbNameCurrent = @DBName END IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName) BEGIN SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13)) END SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13)) FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema END CLOSE cur DEALLOCATE cur SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_2
DECLARE @SqlSyntax AS NVARCHAR(MAX) = '' SET @SqlSyntax += CONCAT(' --== Changing Database Ownership ==--',CHAR(13),CHAR(13),'USE master;',CHAR(13)) SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]') FROM sys.databases AS S JOIN #InvalidUsers AS U ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13)) ------------------------------------------------------------------------------------------ SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N''' ,U.LOGIN_NAME ,''')' ,CHAR(13),CHAR(9) ,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13) ) FROM [sys].[server_principals] AS [SP] JOIN #InvalidUsers AS U ON U.LOGIN_NAME = SP.[name] SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_3
CREATE TABLE #InvalidUsers( [SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ) INSERT INTO #InvalidUsers ( [SID] ,LOGIN_NAME ) EXEC sys.sp_validatelogins ------------------------------------------------------------------------------------------ CREATE TABLE #InvalidUserDbInfo( DBNAME NVARCHAR(128) ,[SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ,OWNED_SCH VARCHAR(128) ) DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) ,@DBName AS NVARCHAR(128) ,@DbNameCurrent AS NVARCHAR(128) = '' ,@SqlSyntax AS NVARCHAR(MAX) = '' ,@OwnedSchema AS VARCHAR(128) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT LOGIN_NAME FROM #InvalidUsers OPEN cur FETCH NEXT FROM cur INTO @LoginName WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = CONCAT( 'USE ?;INSERT INTO #InvalidUserDbInfo SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema FROM sys.database_principals AS DP LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id WHERE [type] = ''U'' and DP.name = ''',@LoginName,'''' ) EXEC sys.sp_MSforeachdb @Sql FETCH NEXT FROM cur INTO @LoginName END CLOSE cur DEALLOCATE cur ------------------------------------------------------------------------------------------ DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT IUDI.DBNAME ,IUDI.LOGIN_NAME ,IUDI.OWNED_SCH FROM #InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME OPEN cur FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema WHILE @@FETCH_STATUS = 0 BEGIN IF @DbNameCurrent <> @DBName BEGIN IF @DbNameCurrent <> '' SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13)) SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13)) SET @DbNameCurrent = @DBName END IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName) BEGIN SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13)) END SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13)) FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema END CLOSE cur DEALLOCATE cur ------------------------------------------------------------------------------------------ SET @SqlSyntax += CONCAT(CHAR(13),CHAR(13) ,' --== Changing Database Ownership ==--',CHAR(13),CHAR(13) ,'USE master;',CHAR(13)) SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]') FROM sys.databases AS S JOIN #InvalidUsers AS U ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13)) ------------------------------------------------------------------------------------------ SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N''' ,U.LOGIN_NAME,''')' ,CHAR(13),CHAR(9) ,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13)) FROM [sys].[server_principals] AS [SP] JOIN #InvalidUsers AS U ON U.LOGIN_NAME = SP.[name] SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_3