Security Questions: Removing Logins From Databases

As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

If an associate leaves, what is the best way to remove them from not only the logins but also all the databases?

A good question about properly managing security.  If you weren’t aware, when a login is dropped from a SQL Server instance, the users associated with that login are not dropped from the databases that the login is mapped to.  This is intended behavior for SQL Server, it boils down the to choice between automatically dropping users with logins or only dropping users when they are specifically dropped.  This helps ensure that when a login is dropped that permissions associated to the users mapped to the login are not dropped at the database level.

Create Logins and Users

Since the question is how to delete a login and the associated users, we’ll first need a database or two and a login and users for the scenario.  The script in Listing 1 creates a login named KillDaUser.  Along with two databases, named SecurityDB1 and SecurityDB2.  In each of the databases, a user, named KillDaUser, is create as well.

--Listing 1. Create a login, databases, and users
USE master
GO

CREATE LOGIN KillDaUser WITH PASSWORD=N'pass@word1'
GO

CREATE DATABASE SecurityDB1
GO

CREATE DATABASE SecurityDB2
GO

USE SecurityDB1
GO

CREATE USER KillDaUser
FOR LOGIN KillDaUser WITH DEFAULT_SCHEMA=dbo
GO

USE SecurityDB2
GO

CREATE USER KillDaUser
FOR LOGIN KillDaUser WITH DEFAULT_SCHEMA=dbo
GO

Drop Logins and Not Users

To first demonstrate the behavior that the question points to, the login KillDaUser will be dropped from the instance.  After dropping the login, query sys.database_principals, using the query in Listing 2.  The results from the query, shown in Figure 1, illustrate the issue the question focuses on.  The login has been dropped but the users are still in the databases.

--Listing 2. Drop login with no extra help

USE master
GO

DROP LOGIN KillDaUser
GO

SELECT 'SecurityDB1', name, type_desc
FROM SecurityDB1.sys.database_principals
WHERE name = 'KillDaUser'
UNION ALL
SELECT 'SecurityDB2', name, type_desc
FROM SecurityDB2.sys.database_principals
WHERE name = 'KillDaUser'
GO

Figure 1. Output from sys.database_principals showing users exist

How then do you drop a login and it’s users with minimal effort?

Drop Logins and Users

The answer is pretty easy.  Before getting to the answer, another login with users will be needed.  Run the script in Listing 3 to create a new login and users in the two databases previously created.

--Listing 3. Add the login and user KillDaOtterUser to the instance and databases
USE master
GO

CREATE LOGIN KillDaOtterUser WITH PASSWORD=N'pass@word1'
GO

USE SecurityDB1
GO

CREATE USER KillDaOtterUser
FOR LOGIN KillDaOtterUser WITH DEFAULT_SCHEMA=dbo
GO

USE SecurityDB2
GO

CREATE USER KillDaOtterUser
FOR LOGIN KillDaOtterUser WITH DEFAULT_SCHEMA=dbo
GO

Now for the answer, probably the easiest way to drop logins and users together is to create a script that loops through all of the databases on an instance and drops the users as well.   As a simple example, one option is to utilize sp_msforeachdb to execute a DROP USER script against each database on the instance, this is shown in Listing 4.  Running the query at the end of the script this time will show that the users no longer exist in the two databases.

--Listing 4. Drop logins and users in a single batch

USE master
GO

DROP LOGIN KillDaOtterUser

EXEC sp_msforeachdb 'USE ?; IF EXISTS(SELECT * FROM sys.database_principals WHERE name = ''KillDaOtterUser'')
DROP USER KillDaOtterUser'

SELECT 'SecurityDB1', name, type_desc
FROM SecurityDB1.sys.database_principals
WHERE name = 'KillDaOtterUser'
UNION ALL
SELECT 'SecurityDB2', name, type_desc
FROM SecurityDB2.sys.database_principals
WHERE name = 'KillDaOtterUser'

Summary

When logins are dropped, SQL Server will not by default remove users.  It’s the way it’s supposed to be and it help ensures that dropping a login has minimal impact on other security settings.  As the scripts show, it is fairly easy to remove users with logins, it just takes a little effort.  The only real curveball in this process is dropping users that are associated with logins that don’t use the same name.  A little digging and extra logic could solve this, but that’s up to you to add in.  What do you think of Microsoft’s decision to require logins and users to be dropped separately?