Yesterday I wrote a blog post discussing how the guest user can become a security threat. The script which was demonstrated in 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 about the Force Removing User.
“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 (https://blog.sqlauthority.com)
18 Comments. Leave new
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..
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!
it’s really a stupid way to add test user account on Production environment.
Pretty sad
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….!!
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.
Hi Pinal,
I think in another way….
But because of that person’s mistake we get solution for such problems….
Thanks Pinal for different notes..
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
It’s Okay. You have given correct reply and we got to know a new feature! :)
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.
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.
we can stop and start the SQL service again and drop the table .
Kumar, I tried this, but is not working after reading and following above slove my problem
How to work as a beginner to professional user in MS SQLSERVER.PLZ give me the guidlines.
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 ?
Yes I agree the comments posted
This is exactly what happens when people don’t follow directions or RTFM. The lazy rule the earth.
Sometimes In SQL 2000 – for those who are still using it :) you get this:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.dm_exec_sessions’.
Well I am glad you posted it. I was trying to drop a user but apparently the user was currently logged in??? I guess even though I disconnected from sql server, the user was still in a “sleeping” status. Dev system of course :)