April 25, 2016 at 9:19 pm
I have a question from a security point of view & everyone seems to have different opinions. so not sure if people understand this exactly.
I have an SQL Login [test\testuser] (windows authenticated account) and this login has access to 2 databases: [dbtest1] & [dbtest2].
If I've been asked to remove this user from the server (not just from 1 of the DB's), is the following command enough.
ALTER LOGIN [test\testuser] DISABLE
I understand this person will no longer be able to log in but there user will not be removed from [dbtest1] & [dbtest2].
Questions:
1. Should I disable or remove the login? is there any difference between the 2 besides losing possibly historical record???
2. If I disabled or Remove should I also remove the access to each DB.
April 26, 2016 at 3:25 am
If you remove the database users, the person can still log in and access the server. Depending on their server-level permissions, they may still be able to access the databases.
If you disable the login, then they cannot log in at all.
Which you do depends what you want to achieve.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2016 at 3:37 am
I prefer the nuclear option - if the login is no longer required, get rid of it. If you have proper change control procedures, you'll have a rollback script to back out the change should you need to. If you're slightly more conservative, disable the login and set a reminder in your diary for two weeks. If nobody has squealed, remove the login at the end of that period.
John
April 26, 2016 at 3:43 am
GilaMonster (4/26/2016)
If you remove the database users, the person can still log in and access the server. Depending on their server-level permissions, they may still be able to access the databases.If you disable the login, then they cannot log in at all.
Which you do depends what you want to achieve.
GilaMonster (4/26/2016)
If you remove the database users, the person can still log in and access the server. Depending on their server-level permissions, they may still be able to access the databases.If you disable the login, then they cannot log in at all.
Which you do depends what you want to achieve.
So if i disable, theres no way they can log in and use the DB.
What about a situation where.
1. Disable or remove login
2. Leave orphaned record available against the DB
3. Use some .net code to call a SP using step 2 user.
If disabling the user does not allow anything, than disabling the user is enough
April 26, 2016 at 3:46 am
John Mitchell-245523 (4/26/2016)
I prefer the nuclear option - if the login is no longer required, get rid of it. If you have proper change control procedures, you'll have a rollback script to back out the change should you need to. If you're slightly more conservative, disable the login and set a reminder in your diary for two weeks. If nobody has squealed, remove the login at the end of that period.John
If i delete the login - which i tend to agree and will do. What about the users? Do you leave them orpharned or do you remove them.
April 26, 2016 at 3:49 am
Remove them first. Nobody wants orphaned users in their database. It shouldn't be too difficult to write a script that will take the login name, go to all databases and drop the users and finally drop the login. You'd need another script to generate the rollback script as well!
John
April 26, 2016 at 3:53 am
Tava (4/26/2016)
3. Use some .net code to call a SP using step 2 user.
To do that, the user running that code requires impersonate permissions on User2. That's a fairly high-level permission.
Whether you leave or remove the user is mostly down to whether you expect to need to re-enable the login at some point in the future. If not, then just remove the users and login entirely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2016 at 3:54 am
Thanks for your help... appreciate it.
April 28, 2016 at 4:23 am
Tava (4/25/2016)
I have a question from a security point of view & everyone seems to have different opinions. so not sure if people understand this exactly.I have an SQL Login [test\testuser] (windows authenticated account) and this login has access to 2 databases: [dbtest1] & [dbtest2].
If I've been asked to remove this user from the server (not just from 1 of the DB's), is the following command enough.
ALTER LOGIN [test\testuser] DISABLE
Disabling will prevent the login from gaining access to the system, even if you drop the login like so
DROP LOGIN [test\testuser]
That will not drop the individual database users
Tava (4/25/2016)
I understand this person will no longer be able to log in but there user will not be removed from [dbtest1] & [dbtest2].
you'll need to remove these manually if required.
Tava (4/25/2016)
Questions:1. Should I disable or remove the login? is there any difference between the 2 besides losing possibly historical record???
No difference really, since the account is a windows authentication account the SID wont change, unless of course it's dropped from AD and re added!
Tava (4/25/2016)
2. If I disabled or Remove should I also remove the access to each DB.
For cleaning up, yes, you would probably want to drop the database users
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 28, 2016 at 11:37 pm
I have an interesting question on this topic and its probably worth testing sometime. What happens if that deleted login owned a SQL Agent Job? SQL Jobs run with the permissions of the job owner. So surely the job should fail. I would be surprised if it didn't fail.
Anything can be fixed
April 29, 2016 at 2:26 am
I think I've seen jobs failing after owner logins have been deleted. But it's easy to test - why don't you try it and let us know how it goes?
John
April 29, 2016 at 5:52 am
I would script out the user permissions ( server/databases/object/column/certificates.. level) before dropping from the server/databases just for the safe side if I need to re-enable. Also check for any job ownership on this sql instance.
Also if you are planning to remove the user from the server, check if the user belongs to an AD group in the sql instance which may needs to be removed by systems team.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply