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.
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
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'
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?