SQLTeam.com | Weblogs | Forums

How to remove the login for individual users


We are planning to remove the individual user login in MS Sql server DB and planning to add the group logins. Can you please let us know which table we can refer for deleting the individual user login and how to differentiate the user login and group login.

You can see the logins available from the system view sys.server_principals. You should not/cannot remove logins by removing entries from that view. You should use the SSMS user interface or a T-SQL query (DROP LOGIN) to remove logins.

Before you remove the logins, remove the database users associated with those logins. Otherwise, the logins will become orphaned. Then, when you remove the users, any permissions you granted to those users will be gone along with that. So after you create the windows group login and corresponding users, you will need to grant permissions to those users. A better alternative might be to create database roles, grant permissions to those roles and add the groups to those roles.

Also sometimes you have to deal with nasty schemas that might have been created for each individual logins. yuuge problem.
So this begs the question why are you doing this in the first place? May I ask why you are giving access to individuals or groups of people access to sql server?

To be brutally honest for you and your company's own good, you don't know enough about security to make these changes or prevent an attack. My recommendation would be to hire a security consultant to come in and fix things up for you and give you a little training on the subject. You are, after all, trying to protect the proverbial keys to your city.

1 Like

Removing individual users and replacing them with windows groups, while potentially reducing management overhead, can actually create a bigger headache as you are then opening up the ability for non-dba's to grant access to your confidential databases simply by adding user to the domain groups. It could be a long time before you realise you have users accessing databases that you did not set up.