Domain group members cannot connect [SQL Server 2k5 Workgroup Ed.]

We have a brand new out-of-the-box SQL Server 2005 Workgroup Edition install
.
We are using Windows Authentication, and have created SQL logins for about
40 different groups on our domain. We've given those logins the appropriate
permissions on the databases they're supposed to be able to access.
The problem is that when users try to connect to the SQL server, they cannot
connect. An error 18456 is thrown, and logged in the Application event log
stating "Login failed for user OURDOMAIN\theuser" (example values). The
user is properly a member of group added as a login to SQL Server, and we've
confirmed that there are not conflicting permissions that would deny those
users access via another route.
This is only a problem for domain-based groups. If we create a local group
on the SQL server machine, through Computer Management -> Local Users and
Groups, then make the same users a member of THAT group, and finally then
follow the same process to add that local group to SQL Server Logins and set
the database privileges, it works!!
Our group memberships change frequently, and are used for a lot more than
just SQL server permissions. So, using local groups and maintaining
membership in both places is not really feasible. Any ideas why a local
machine group containing domain user accounts would work fine, but a domain
group containing the same accounts would not?
Thanks in advance.
~JimIs the server that SQL is on joined to the domain?
Jim Kilmer wrote:
> We have a brand new out-of-the-box SQL Server 2005 Workgroup Edition insta
ll.
> We are using Windows Authentication, and have created SQL logins for about
> 40 different groups on our domain. We've given those logins the appropria
te
> permissions on the databases they're supposed to be able to access.
> The problem is that when users try to connect to the SQL server, they cann
ot
> connect. An error 18456 is thrown, and logged in the Application event lo
g
> stating "Login failed for user OURDOMAIN\theuser" (example values). The
> user is properly a member of group added as a login to SQL Server, and we'
ve
> confirmed that there are not conflicting permissions that would deny those
> users access via another route.
> This is only a problem for domain-based groups. If we create a local grou
p
> on the SQL server machine, through Computer Management -> Local Users and
> Groups, then make the same users a member of THAT group, and finally the
n
> follow the same process to add that local group to SQL Server Logins and s
et
> the database privileges, it works!!
> Our group memberships change frequently, and are used for a lot more than
> just SQL server permissions. So, using local groups and maintaining
> membership in both places is not really feasible. Any ideas why a local
> machine group containing domain user accounts would work fine, but a domai
n
> group containing the same accounts would not?
> Thanks in advance.
> ~Jim

Domain Group Members and Database Roles

Greetings...
I have a SQL2005 Express database where I have created 3 user defined
database roles, granted those db roles all the required permissions on the
required db objects, and made a few (Domain\Group) groups login members of
those database roles. The 3 roles are for high, medium, and low rights.
When a user logins with Windows NT authority, I can determine their identity
with
SUSER_SNAME(), but how can I easily determine what role or roles the user,
not the group, belongs to? The user would not have a principal_id, but the
group does. There's got to be a way to know what role the USER is in, isn't
there?
DaveHi
Try
sys.database_role_members catalog view
"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
> Greetings...
> I have a SQL2005 Express database where I have created 3 user defined
> database roles, granted those db roles all the required permissions on the
> required db objects, and made a few (Domain\Group) groups login members of
> those database roles. The 3 roles are for high, medium, and low rights.
> When a user logins with Windows NT authority, I can determine their
> identity
> with
> SUSER_SNAME(), but how can I easily determine what role or roles the user,
> not the group, belongs to? The user would not have a principal_id, but the
> group does. There's got to be a way to know what role the USER is in,
> isn't
> there?
> Dave|||Uri,
that view is not too informative... I finally got this query to work for
what I want:
Select name as Roles
From sys.database_principals
Where type='R' And is_fixed_role=0 And principal_id<>0
But I wouldn't call this 'easy' or intuitive...
Dave
"Uri Dimant" wrote:

> Hi
> Try
> sys.database_role_members catalog view
>
> "DGardner" <DGardner@.discussions.microsoft.com> wrote in message
> news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
>
>|||Hi Dave.
Try with the IS_MEMBER function
Regards
Antonio Soto
Solid Quality Learning
http://www.sqlu.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"DGardner" <DGardner@.discussions.microsoft.com> escribi en el mensaje
news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
> Greetings...
> I have a SQL2005 Express database where I have created 3 user defined
> database roles, granted those db roles all the required permissions on the
> required db objects, and made a few (Domain\Group) groups login members of
> those database roles. The 3 roles are for high, medium, and low rights.
> When a user logins with Windows NT authority, I can determine their
> identity
> with
> SUSER_SNAME(), but how can I easily determine what role or roles the user,
> not the group, belongs to? The user would not have a principal_id, but the
> group does. There's got to be a way to know what role the USER is in,
> isn't
> there?
> Dave|||Antonio,
Sorry... IS_MEMBER() doesn't work either. Although the group is assigned to
the role, the individual user who is a member of the group is not, and so
IS_MEMBER() returns 0 every time. It looks like the query I constructed is
the only way to get the info I want.
Dave
"Antonio Soto" wrote:

> Hi Dave.
> Try with the IS_MEMBER function
> Regards
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "DGardner" <DGardner@.discussions.microsoft.com> escribió en el mensaje
> news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
>
>