SQL SERVER – Force Removing User from Database – Fix: Error: Could not drop login ‘test’ as the user is currently logged in.

Yesterday I wrote a blog post discussing how guest user can become security threat. The script which was demonstrated for the example had a small T-SQL query which creates a new user. Later, I got an email from a user who had created this scenario on his production environment. It makes me sad that I had clearly talked multiple times about how to execute this as a trial on a development server or a test server, but NOT on a production server. Anyway, here is the email:

“Pinal,

I ran your test on our production server as our development server was busy. Now when I try to drop the test user, it gives me the following error:

Msg 15434, Level 16, State 1, Line 1
Could not drop login ‘test’ as the user is currently logged in.

Could you please help me drop this test user? I cannot restart the server as it is a production server, and I need to drop this user as fast as I can. First of all, I have closed all the connections but I do not know which connection is still open. I just need to identify which connection is open and kill it if required. In any case, I need to drop it before my supervisor catches me.”

This email I read really felt very awkward. Here is my answer. I hope you do not find it rude.

“Dear Friend,

First of all, I want to ask why you ran the demo on your production server. You must not execute it on a production server. It was just for the purpose of demonstration. This reminds me of an email I received from one of my friends that one should never post details which can harm a certain production server, as there are people who do not follow the advice no matter how many times you warn them.

Trust me – your supervisor will have configured audits and he will soon discover this test user, whether you tell him or not. I suggest that instead of hiding, you go and tell him everything in the most honest way you can.

Here is one more tip for you – killing any sessions without permission from a user or a senior admin will get you in trouble.

Once again I do not want your server to be exposed to potential security threats while your supervisor is away, so follow the steps mentioned below to drop the test user:

Find an active connection using the test user:

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test'

Kill the active connection using the test user:

KILL 52 -- Replace 52 with the your session ids received from earlier query

Drop the test user:

DROP LOGIN test

Hopefully you’d keep your production server away from executing scripts meant to run on a development server.”

Well, after writing the email I did not feel good as I did not like to sound rude. However, I kept telling myself that it was needed. What do you think about this whole conversation?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

20 thoughts on “SQL SERVER – Force Removing User from Database – Fix: Error: Could not drop login ‘test’ as the user is currently logged in.

  1. Sir,

    It was not rude, it was needed and whatever you told that was right cause you already have warned and mentioned in yesterday post to not to be run in Production environment and he did the same by saying ‘development server was busy’, it doesn’t mean that if Dev/Test are busy then try on Prod..

    Anyway both the post are great and I learned lot new things.
    Thanks..

    Like

  2. Pinal, if you think that might be rude, then you would consider me an absolute cave man! There was nothing wrong in what you said. Its the truth, posting this experience helps us all. thanks!

    Like

  3. Hi pinal,

    Good day..

    When i open this blog first thought is my eyes are forced me to watch the neat picture that was make me crazy what was the inside story behind it,when i deeply walk through on the actual story it was a bit pain that we should not do these kind of things without any knowledge,

    it reminds me the way of behavior with work..!

    thanks for sharing with us….!!

    Like

  4. Pinal,

    I have one thought in my mind,i want to share with u,

    in feature if we have these kind of things please try to avoid notes about “Don’t” do things, some times people are crazy about Don’t do things they never keep them quite…

    this is my idea only…!!! ignore if it’s not good.

    Like

  5. omg, if you call that rude then i m ready to work with you … it was way to gentle and anything but remotely rude. ur humility is praiseworthy

    Like

  6. Some times even the test environment can be considered critical as well. In my previous assignment, I was trying something on our test cluster, and an hour late, I came to know the Dev team needs that. I was not happy to stay back the whole night to fix it.

    So a better approach would be to try in your own LAB… You are the admin, and you can play with it.

    Like

  7. I think you did the right thing regardless of the tone. I once worked with a guy who decided to drop an important “TableSpace” on a production Oracle instance. The only reason it wasn’t a complete disaster was that there were transactions present which made dropping the space impossible.

    Like

  8. Pingback: SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Identifying guest User using Policy Based Management « SQL Server Journey with SQL Authority

  10. Sometimes when going to restore my database from .bak file, I get the following sort of message :

    “Could not restore database, currently in use by this session”.

    Should I use the same trick for this problem too ? Is it ok, or could cause something awkard ?

    Like

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s