SQL Server DDL Trigger to Prevent Logins Deletion

By: Svetlana Golovko   |   Updated: 2021-11-16   |   Comments   |   Related: > Triggers

Problem

Dropping SQL Server logins without dropping associated the database users creates orphaned database users. By default, the DROP LOGIN statement in SQL Server only removes logins, but not database(s) users that are linked to these logins. The best practice is to delete all databases users that are mapped to this login first and then delete the login itself. When logins are deleted, and users remain orphaned, DBAs have a cleanup task to perform from time to time. This is to make sure that an orphaned user is not misused (to prevent a potential security issue). How can we make sure that we don’t forget to delete database users before dropping logins?

Solution

There is a tip that describes some potential problems with orphaned users and how to fix the issues in a SQL Server database. This Microsoft article also has information about troubleshooting orphaned users. What we want to do is to make sure that a login can’t be deleted if there are any users in databases.

In this tip we will use SQL Server DDL Triggers to prevent login deletion if they are going to introduce orphaned user issues.

SQL Server Orphaned User Example

Let’s reproduce the issue with an orphaned user first.

We are going to create a database, a login, and a user for this login in our new database.

USE [master]
GO


-- create demo database
CREATE DATABASE [_DemoSG]
GO

-- create demo login
CREATE LOGIN [sam] WITH PASSWORD=N'T3mpUs3r2T3$t1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

-- create demo user
USE [_DemoSG]
GO


CREATE USER [sam] FOR LOGIN [sam]
GO

Now we will drop the login and check database user’s information:

USE [master]
GO

DROP LOGIN sam
GO

USE [_DemoSG]
GO

SELECT d.type_desc, d.sid, d.name AS username , s.sid as login_sid
FROM sys.database_principals AS d  
LEFT JOIN sys.server_principals AS s ON d.sid = s.sid  
WHERE s.sid IS NULL  
  AND d.authentication_type_desc = 'INSTANCE' 

We have an orphaned user now (a user without a login):

SQL Server DDL Trigger

We will create a DDL trigger that will stop us from dropping a login if it is a database user.

USE [master]
GO
CREATE TRIGGER ddl_trig_drop_login   
ON ALL SERVER   
FOR DROP_LOGIN   
AS  
SET NOCOUNT ON

DECLARE @sid VARBINARY (86), @login_name SYSNAME

   -- get login name and sid from Eventdata
   SELECT @login_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname'), 
                     @sid = EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]','varbinary(86)') 
   
   PRINT 'Deleting "'+ @login_name+ '" login...'  

   -- check if login has user account in any databases
   CREATE TABLE ##tl (sid VARBINARY (86) NOT NULL, dbname SYSNAME NOT NULL)
   CREATE TABLE  ##t_sid (sid VARBINARY (86)  NOT NULL)

   INSERT INTO ##t_sid VALUES(@sid )
   
   EXEC master.sys.sp_msforEachdb 
      'DECLARE @sid VARBINARY (86)    
      SELECT @sid = sid FROM ##t_sid
      
      IF EXISTS (SELECT  sid FROM [?]..sysusers  WHERE sid = @sid) 
      BEGIN 
         INSERT INTO ##tl VALUES (@sid, ''?'')
      END '

   IF (SELECT count(sid) FROM ##tl) > 0
   BEGIN
      DECLARE @dbs VARCHAR(1500), @message VARCHAR(1800)

      SELECT @dbs = COALESCE(@dbs + ',', '') + dbname
      FROM ##tl
      ORDER by dbname

      SELECT @message =  'Login can''t be deleted. Please, remove user(s) mapped to this login from ' + @dbs + ' database(s)...'

      PRINT @message 
      
      DROP TABLE IF EXISTS ##tl
      DROP TABLE IF EXISTS ##t_sid

   ROLLBACK
   END

   DROP TABLE IF EXISTS ##tl
   DROP TABLE IF EXISTS ##t_sid
GO  

In our trigger we used the EVENTDATA function and the undocumented Microsoft stored procedure sp_MsForEachdb.

You can find xml elements for EVENTDATA here. To find valid elements for the DROP LOGIN search for the "EVENT_INSTANCE_DROP_LOGIN" in the event.xsd. We used "ObjectName" for extracting the login name and "SID" element for a login’s SID.

Testing the DDL Trigger in SQL Server

Let’s create a new login and a user for this login in a database:

USE [master]
GO

CREATE LOGIN [mat] WITH PASSWORD=N'T3mpUs3r2T3$t2', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

USE [_DemoSG]
GO

CREATE USER [mat] FOR LOGIN [mat]
GO

Now let’s try to drop this login:

DROP LOGIN [mat]
GO

We get an error message that says the login is a user of a database:

After we delete the user from the database and retry the login delete we are successful:

Next Steps

• Find another example of the DDL trigger here.
• We use XQuery for xml query.
• Check DBA best practices tips here.
• Read other SQL Server Triggers Tips.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips