我怎么可以查询该活动目录帐户与SQL Server用户关联的？ [英] How can I query an which Active Directory account is associated with a SQL Server user?

问题描述

我有两个SQL Server的，我主要使用（2005年实例和2000年），我的权限结构的工作原理，例如 -

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

首先，我创建一个Active Directory组，然后我添加所有必要的用户给它。然后，我去SQL-MS和我通过增加一个用户选择Windows身份验证选项，然后选择我刚刚创建的AD组。不恰当的这个职位，但我那么新的登录帐户与所有必要的数据表，视图和SP进行关联。

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.

选择组后，我一直留在登录名称字段作为AD组，以供参考的名称。

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

最近我有一个AD组重新命名。该数据库继续工作，并且一些如何，SQL Server知道哪些广告组SQL登录相关联。我的问题是，登录名没有在SQL Server的更新，所以我没有这AD组与SQL Server的登录帐户相关联的线索！

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!

有，我可以运行一个查询，或者是有埋设置一些地方，可以帮助我发现哪个AD组与该帐户相关联？

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?

- 编辑 -

感谢的反应你的答案。你已经回答了这个问题，但是，它的propgated另一个问题<一href="http://stackoverflow.com/questions/6831933/how-can-i-obtain-an-active-directory-group-name-from-a-sql-server-stored-sid">posted这里。

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

推荐答案

您可以检查您已在系统中定义为登录Windows组;

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

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

这适用于 SQL Server 2005和较新的而已。

但你不会得到实际的AD组的名字 - 只有SID该组......

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

整个安全系统是SQL Server 2000上完全不同的 - 我不认为有一个1：1等值查询老恐龙:-)最好的，我能想到的是：

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

但不幸的是，没有办法，我会知道的用户Windows之间的分离和Windows安全性的集团这里....

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

这篇关于我怎么可以查询该活动目录帐户与SQL Server用户关联的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！