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?
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.
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?