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://darkslategrey-bat-805937.hostingersite.com)
