August 20, 2009 at 7:54 am
Hi,
I'm trying to grant a workgroup read access to only 1 view in my SQL Server 2005 db. The workgroup, Dow-wg, exists on our Windows network and I can add it as a Login in Sql Server.
I've created a schema, Dow, in my db and the only object in it is Dow.Dow_View. I'm not sure who should own the schema, currently it's db_datareader.
Problem 1, is when I assign Dow-wg select permission on the Dow schema, the user who's testing for me cannot access the view. (He is in the group.) If I assign the same permission directly to the user, he can see the view. This is a big problem, since the members of the group will change every few months.
Problem 2 is that the user also has access to objects in INFORMATION_SCHEMA and sys. Do I have to deny access to these schemas or should I just leave them.
Thanks for your help.
Linda
August 21, 2009 at 1:00 pm
For problem 1, do you mean Windows security group?
For problem 2, you should leave them alone. SQL Server 2005/2008 will only show through those objects information on the objects a particular user has access to.
K. Brian Kelley
@kbriankelley
August 21, 2009 at 1:41 pm
2. I like easy answers like that.
1. I'm not sure, it's an account in our WIndows domain, that has access to a specific folder and no one who is not in the group has access to that folder.
Thanks,
Linda
August 21, 2009 at 1:59 pm
Okay, that's a Windows security group.
1. Make sure it is added as a login to the SQL Server.
2. Make sure that login is added as a user to the particular database.
3. Verify the permissions on that user.
4. Verify that the person you are testing with was a member of the group before he/she logged on last. The reason I say this is when you log on to your computer, a security token is created. It contains information on you and all the groups you are a member of. If you're added to a group after you've logged in, then your security token will not reflect that until you log off and log back on to create a new security token. This is a Windows behavior, not a SQL Server one.
K. Brian Kelley
@kbriankelley
August 21, 2009 at 2:18 pm
Yep, I did that. And the user was an existing member of the group.
I did not assign the group a database role membership. In Securables, I added the Dow schema and granted Select. Maybe it's a problem with the way the group was set up.
August 21, 2009 at 2:23 pm
In addition to the previous poster - you cannot define a default schema to a Windows Group. If the user does not schema qualify access to the object, the object will not be found. For a user who is a member of the group:
This does not work: SELECT * FROM table;
This does work: SELECT * FROM dow.table;
I recommend that everyone get in the habit of schema qualifying all object access.
The other option would be to create a synonym in the dbo schema for your tables in the dow schema. For example:
CREATE SYNONYM dbo.table FOR dow.table;
But, you need to understand that accessing objects without the schema will create additional entries in the execution cache for every user that accesses that object without schema qualifying it. Instead of a single execution plan for a query, you could end up with hundreds - one for each user. Using the synonym above might reduce that - but I have not tested it myself.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 21, 2009 at 3:05 pm
FYI, it is a problem with the Windows group.
Thanks for the help!
Linda
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply