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
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'
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?
18 thoughts on “Security Questions: Removing Logins From Databases”
I want to remove all individual user logins from single database
eg. I have one database ABC and 5 users can access ABC database,
Now my requirement is I have created one group and placed these 5 users in my new group, So now individual user login are left as it is. I want to remove all 5 users at a time while deployment.
Help would be appreciated…:)
I just saw this post. Very helpful, especially if you have an instance with lots of databases. However, if you drop a login, & some users are left (maybe didn’t use your script), you have orphans, i.e., users that have no login associated with them. If the user account does not have the same name as the login, it may be difficult to go back later and clean up user accounts that do not map to a logins. —David Shink
Thanks for the comment. Yes, my script relies on parity with the name between the instance and the databases. You could also use the SID to match up the users and logins, that would probably have been a better solution.
Thanks for the great article. Created a script to handle the “curveball” situation. http://sqlzen.blogspot.com/2013/07/delete-database-accounts-for-login.html
Your welcome. Nice post to you, as well.
caution – user and login names don’t have to be the same. Link above implicitly
assumes just that.
That could definitely be an issue. A proper script would use the logon sid to ensure the right accounts were being deleted.
Nicely done Jason, I personally don’t have a problem with them being separate. I’m not sure if I’ve just accepted that is the function of how it is or if I am genuinely just okay with it. From a security standpoint I see the need based on impact. Thanks for sharing the scripts; they are spot on.
Thanks, Chris. Another reason why this may make sense as well is with the new contained database users. I should have added in some on that in the post as well.
Comments are closed.