SQL Server DDL Trigger to Prevent Logins Deletion


By:   |   Updated: 2021-11-16   |   Comments   |   Related: More > 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):

Orphaned user query

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:

Error deleting login with a user in a database

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

Drop user and login
Next Steps





get scripts

next tip button



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

View all my tips


Article Last Updated: 2021-11-16

Comments For This Article





download














vote


get free sql tips
agree to terms