0

I am having an issue logging into a SQL Server 2000 Database via Windows Authentication, where access has been granted at the database level via an Active Directory group, to which the user in question is a member of.

I can check the user's AD group membership via: whoami /groups and see:

somedomain\SalesDB-RO                   Group            S-1-5-21-2172273820-3134075794-738947201-31792  Mandatory group, Enabled by default, Enabled group             
somedomain\SalesDB-RO                   Group            S-1-5-21-923798017-1667202166-518595180-7612    Mandatory group, Enabled by default, Enabled group

Due (somehow) to the nature of Active Directory SID History, there are two entries.

Now, I can look in the database (using SSMS, using another account) and see that 'SalesDB-RO' does in fact have db_datareader permissions in the database, yet the user still cannot login. I'm wondering if perhaps the 'SalesDB-RO' group is textually the same, but perhaps has a different SID within the SQL Server database.

How would I view the SID assigned to the 'SalesDB-RO' group within the SQL Server? (I do not personally have SysAdmin privileges, but could get someone that does to execute any necessary commands).

Secondary question:
On another SQL Server (2008) instance where I do have sysadmin rights, I see SID's are of the form:

0x0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF01234567

....whereas from AD they are in the form: 

S-1-5-21-2172273820-3134075794-738947201-31792

Is there a way to get comparable versions of the SID's?

UPDATE

As it turns out the issue was the user was a member of an Active Directory Group that was DENIED read permissions on the database.

tbone
  • 5,715
  • 20
  • 87
  • 134
  • It's not exactly answering your question, but you're trying to troubleshoot why an AD group can't access a db. Have you checked to see that the AD group is set to be a security group (as opposed to a distribution group)? I know that's tripped me up before. – Ben Thul Jun 07 '12 at 02:18
  • @Ben: It seems to be working for other people (including myself) so don't think that's the issue – tbone Jun 07 '12 at 16:08
  • duplicate of http://stackoverflow.com/questions/6831933/how-can-i-obtain-an-active-directory-group-name-from-a-sql-server-stored-sid/ – Kodak Apr 12 '17 at 13:30

1 Answers1

0

As it turns out, in my case the issue was the user was a member of an Active Directory Group that was DENIED read permissions on the database.

Better (more relevant to the question) info here (credit @Kodak):

How can I obtain an Active Directory Group name from a SQL Server stored SID?

Community
  • 1
  • 1
tbone
  • 5,715
  • 20
  • 87
  • 134