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]<br />GO
<br />-- create demo database<br />CREATE DATABASE [_DemoSG]<br />GO<br />
-- create demo login<br />CREATE LOGIN [sam] WITH PASSWORD=N'T3mpUs3r2T3$t1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON<br />GO<br />
-- create demo user<br />USE [_DemoSG]<br />GO
<br />CREATE USER [sam] FOR LOGIN [sam]<br />GO
Now we will drop the login and check database user’s information:
USE [master]<br />GO<br />
DROP LOGIN sam<br />GO<br /><br />USE [_DemoSG]<br />GO<br />
SELECT d.type_desc, d.sid, d.name AS username , s.sid as login_sid<br />FROM sys.database_principals AS d <br />LEFT JOIN sys.server_principals AS s ON d.sid = s.sid <br />WHERE s.sid IS NULL <br /> 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]<br />GO<br />CREATE TRIGGER ddl_trig_drop_login <br />ON ALL SERVER <br />FOR DROP_LOGIN <br />AS <br />SET NOCOUNT ON<br /><br />DECLARE @sid VARBINARY (86), @login_name SYSNAME<br /><br /> -- get login name and sid from Eventdata<br /> SELECT @login_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname'), <br /> @sid = EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]','varbinary(86)') <br /> <br /> PRINT 'Deleting "'+ @login_name+ '" login...' <br /><br /> -- check if login has user account in any databases<br /> CREATE TABLE ##tl (sid VARBINARY (86) NOT NULL, dbname SYSNAME NOT NULL)<br /> CREATE TABLE ##t_sid (sid VARBINARY (86) NOT NULL)<br /><br /> INSERT INTO ##t_sid VALUES(@sid )<br /> <br /> EXEC master.sys.sp_msforEachdb <br /> 'DECLARE @sid VARBINARY (86) <br /> SELECT @sid = sid FROM ##t_sid<br /> <br /> IF EXISTS (SELECT sid FROM [?]..sysusers WHERE sid = @sid) <br /> BEGIN <br /> INSERT INTO ##tl VALUES (@sid, ''?'')<br /> END '<br /><br /> IF (SELECT count(sid) FROM ##tl) > 0<br /> BEGIN<br /> DECLARE @dbs VARCHAR(1500), @message VARCHAR(1800)<br /><br /> SELECT @dbs = COALESCE(@dbs + ',', '') + dbname<br /> FROM ##tl<br /> ORDER by dbname<br /><br /> SELECT @message = 'Login can''t be deleted. Please, remove user(s) mapped to this login from ' + @dbs + ' database(s)...'<br /><br /> PRINT @message <br /> <br /> DROP TABLE IF EXISTS ##tl<br /> DROP TABLE IF EXISTS ##t_sid<br /><br /> ROLLBACK<br /> END<br /><br /> DROP TABLE IF EXISTS ##tl<br /> DROP TABLE IF EXISTS ##t_sid<br />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]<br />GO
<br />CREATE LOGIN [mat] WITH PASSWORD=N'T3mpUs3r2T3$t2', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON<br />GO<br />
USE [_DemoSG]<br />GO<br />
CREATE USER [mat] FOR LOGIN [mat]<br />GO
Now let’s try to drop this login:
DROP LOGIN [mat]<br />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.
Svetlana has been working in IT for more than 17 years. Most of her career has focused on Database Administration (both SQL Server and Oracle) and Database Development. Databases are Svetlana’s passion, but she also has fun helping co-workers and friends in troubleshooting non-database related issues. Svetlana tries to explore and learn as many SQL Server features as possible. Her favorite SQL Server features are Policy Based Management, SSIS, SSRS and Master Data Services. One of Svetlana’s areas of expertize is cross systems / database integration. Svetlana is currently a hands-on Database Team Lead in Calgary, Canada where she promotes SQL Server.
Svetlana likes to share her knowledge with others and enjoys learning herself. Her hobby is photography, but now she spends her free time away from Database Administration with her little girl who proudly wears her MSSQLTips shirt. Svetlana blogs at http://databaserefresh.com and posts her pictures to https://plus.google.com/u/0/111115767149899859037/posts. Her Twitter account is @magasvs.
- MSSQLTips Awards: Rising Star (50+ tips) – 2018 | Author of the Year Contender – 2015-2017
