Need to remove extra logins, how to find out current login context

Question

Hello All,

I have a task to remove  'extra' windows logins from sql server which were created individually. According to current practice we want to add users to existing AD groups which already have logins created for them on sql server. This practice was not followed in a past and we want to fix it.

First step would be to identify which of the logins (and we have quite a few) exist not because of their membership in AD group but because they were  created individually.

Can you please advise if there is a way to find out if this or another db user exists in a database because it was created individually or his id was created in the database because he/she logged in to sql server and a member of AD group.

I also noticed that if user logs into sql server, his db user is not created unless he creates an object.

Thank you, Gene.

---

gene

Answer 1

I don't think you can tell the difference between a login or user that was explicitly created vs. one that was created by SQL Server.

Note that the user also is created if they are granted or denied a permission.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

I think you have to identify your self of individual ID because as you said it is an Windows Id, you can collect the all <Domain>\ID on SQL level and verify that still the users exists(if you have the better quarter audit from the business surely you will have the information whether that user is still works or left the business so that you can take the decisions).. ..also if you have enable the audit of successful/failure login on SQL level then you can use the errorlog to get the frequent login details...also you can check the previous history as well...

 How to find, when the login last used on SQL Server?
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/790e0f14-87e3-4be8-835a-741dc0c8634f/

the option which you have taken is the right one i,e creating individual ID at the Domain level then add those ID to the part of the group which you have created on SQL level but you need to create that group on SQL with what ever permission you needed then ask your AD team add the required ID to part of that group in case if you donot have the accesses...

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.