1

I have have two SQL Server's that I primarily use (a 2005 instance and a 2000.) My permission structure works as such--

First I create an Active Directory Group and then I add all necessary user's to it. Then, I go to SQL-MS and I add a user by select the Windows Authentication option, and then selecting the AD Group which I just created. Impertinent to this post but I then associate the new login account with all of the necessary data tables, views and SPs.

After selecting the group, I have always left the Login name field as the name of the AD Group for reference.

Recently I have had an AD Group renamed. The database has continued to work and, some how, SQL Server knows which AD Group to associate the SQL login. My problem is that the login name hasn't updated in SQL Server so I have no clue which AD Group is associated with the SQL Server Login account!

Is there a query which I can run, or is there a setting buried some where that could help me discover which AD Group is associated with this account?

-- EDIT --

Thank's responders for your answers. You've answered this question, however, it's propgated another question posted here.

Community
  • 1
  • 1
RLH
  • 15,230
  • 22
  • 98
  • 182
  • So far, two good answers that work for 2005. I've updated my question to reflect that I need a solution for both SQL 2005 and SQL 2000. First one to provide a solution for both cases get's the solution check. – RLH Jul 26 '11 at 14:01
  • as I said - the whole security subsystem in 2000 was totally different, so I highly doubt there will be any solution that works for both the 2000 as well as the 2005/2008/2008 R2 versions.... – marc_s Jul 26 '11 at 14:04

2 Answers2

2

The mapping between the AD group and the SQL Server login is being done using the group's SID. You can see the list of logins with their SIDs using sys.server_principals. If you want to change the name of the existing login, you can use ALTER LOGIN.

Nicole Calinoiu
  • 20,843
  • 2
  • 44
  • 49
  • EDIT--Is there an equivalent to the sys.server_principals table for SQL 2000? I just tried to query it and I was told it didn't exist... the same query, however, worked in my SQL 2005 server. – RLH Jul 26 '11 at 13:52
1

You can check that Windows groups you have defined on your system as login;

SELECT *
FROM sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP'

This works on SQL Server 2005 and newer only.

But you won't get the actual AD group name - only the "SID" for that group ....

The whole security system was very different on SQL Server 2000 - I don't think there's a 1:1 equivalent query for that old dinosaur :-) The best I can think of would be:

SELECT *
FROM master.dbo.sysxlogins
WHERE password IS NULL 
  AND name IS NOT null

But unfortunately, there's no way I would be aware of to separate between Windows users and Windows security groups here....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Please see my question in Nicole's answer. – RLH Jul 26 '11 at 13:59
  • "Time to upgrade". Yeah, I agree. However, the corporate 'gods' are always slow to migrate, which is why we are still stuck with Windows XP and, I believe, will continue to be for another 3 years (realistically!) – RLH Jul 26 '11 at 14:02