Jay
asked on
SQL - Grant group access to database fails
Hoping someone can help me out here.
I've create a database in SQL 2005 and need to have users access it via Excel.
If I gran login access to a user account, excel quite happily connects, all is good.
I however want to grant access with an AD global group instead of per user. When I do so, using the same server & database permissions, Excel gives me a login failed.
To be clear, I'm assigning my database as the default database in the General tab.
Server Roles - Public (default)
User Mapping - public, db_datareader
In the server event log I get a Failure Audit on the user (when I've assigned group access to the database), event ID 18456.
Can't figure this out. Still searching...
Thank you for your time.
I've create a database in SQL 2005 and need to have users access it via Excel.
If I gran login access to a user account, excel quite happily connects, all is good.
I however want to grant access with an AD global group instead of per user. When I do so, using the same server & database permissions, Excel gives me a login failed.
To be clear, I'm assigning my database as the default database in the General tab.
Server Roles - Public (default)
User Mapping - public, db_datareader
In the server event log I get a Failure Audit on the user (when I've assigned group access to the database), event ID 18456.
Can't figure this out. Still searching...
Thank you for your time.
ASKER
Thank you. I went through it but didn't find anything that matched my issue.
So specifically, I'm getting "severity 14, state 16".
Everything Ive read so far point to the login not being set to an active database, yet like I said, the same setup on a user account works fine.
So specifically, I'm getting "severity 14, state 16".
Everything Ive read so far point to the login not being set to an active database, yet like I said, the same setup on a user account works fine.
So to be clear, you have created a global group in AD and added this user, that worked when used alone, to this group.
Then you went into SQL and added this Global Group to Login and set a default database to your database, then mapped this group to a database and assigned db_datareader.
Is that where you are now?
Then you went into SQL and added this Global Group to Login and set a default database to your database, then mapped this group to a database and assigned db_datareader.
Is that where you are now?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dbaduck - That sounds about right.
Under Security/Logins, I added the user account, assigned the database, and assigned permissions db_datareader.
Works fine.
Same procedure except using the global group doesn't work.
I'm assuming i should be assinging the database under User Mapping? Right ? Thats where i would normally select the database and then assign db_datareader.
Server Roles is set to Public
nothing else is changed.
Under Security/Logins, I added the user account, assigned the database, and assigned permissions db_datareader.
Works fine.
Same procedure except using the global group doesn't work.
I'm assuming i should be assinging the database under User Mapping? Right ? Thats where i would normally select the database and then assign db_datareader.
Server Roles is set to Public
nothing else is changed.
http://blogs.msdn.com/b/sq