This could be very tedious task since you need to remove the permission from all the databases in the server which users has access to prior removing the SQL login. It could be more painful when the user is owning a schema in the database. Then you need to assign the ownership of the schema to [dbo] prior dropping the permission from the database.
We will see how we can achieve this step by step and finally we will make a script which we can run and ultimately produce a script which we can use to clean up the SQL Server.
1. Frist we need to find out the logins which SQL Server is considered as invalid. We can use a system stored procedure "sys.sp_validatelogins" for this.
CREATE TABLE #InvalidUsers( [SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ) INSERT INTO #InvalidUsers ( [SID] ,LOGIN_NAME ) EXEC sys.sp_validatelogins
2. Next we need to iterate each user in all the databases on the server and remove them from the databases. We will generate a script and use it to drop users. We need to consider the following during the script generation.
- 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
We will use the details populated into the table #InvalidUserDbInfo to generate a script to drop users from databases, change database ownerships and change schema ownerships.
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
This will return a clickable result set. Upon clicking you will get the script to remove users from databases.
3. Finally we consider the database ownerships and drop the login from the SQL Server Instance
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
This will generate a script similar to the one shown below.
Following is the full script which I have combine all the steps and which can be executed in a one go. You can download the script from the following link:
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
Hope this will be very useful to you!
No comments:
New comments are not allowed.