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!
