Monday 1 November 2021

Removing Invalid Logins (Both Server and Database Principals) from SQL Server

Have you ever come across a situation where you need to remove SQL Logins of users which have already left the organisation and not valid anymore ?

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: