February 10, 2010 at 1:15 am
I created an Active Directory logon\user and assigned it to the db_datareader role in a given database. The database contains a Developer schema (and others) but no "dbo" schema. I found that, under Management Studio, the user was unable to see any tables or views despite the fact that he belonged to db_datareader! More amazing is that that user could still issue a SELECT query against tables in the Developer schema and successfully retrieve data (despite the fact that he could not see those tables under SSMS)
1) Can anyone explain this behavior? It appears that db_datareader is restricted to the "dbo" schema. Is it correct? If so, is this documented anywhere?
I found that I needed to add that user to the db_datareader "role" and the "Developer" role for him to see the tables and views in the database.
2) Can anyone explain this behavior?
TIA,
Barkingdog
February 10, 2010 at 10:16 am
I am not seeing this behavior on my test system.
What is the default schema for the user in that database?
What principal owns the developer schema?
My gut feeling is that you have a broken ownership chain - and that is why the permissions are not working correctly. As long as the schema is owned by dbo, there shouldn't be any problems with permissions in the db_datareader role.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 10, 2010 at 12:57 pm
>>>> I am not seeing this behavior on my test system.
Really! That in itself is good to know.
>>>> What is the default schema for the user in that database?
"dbo". Maybe it should be "db_datareader" !
>>>>What principal owns the developer schema?
The database role called "Developer", not "dbo". (And "dbo" own the Developer role.)
>>>> As long as the schema is owned by dbo, there shouldn't be any problems with permissions in the db_datareader role.
So is "dbo" owning the schema a best practice? Microsoft certainly does not follow that approach for their fixed databxze roles (we used their pattern to create our Developer role and schema).
TIA,
Barkingdog
February 10, 2010 at 1:51 pm
I have not found a reason yet to create a schema that was not owned by dbo. Not to say that there isn't one, but I just have not found one.
The default schema for a user defines what schema will be searched first. If the user/role has access to both schemas, then the objects should be accessible by schema qualifying them.
On my system, the db_datareader role is owned by 'dbo'. Is this not the same on your system? If not, somebody changed it because all of the built in roles on my system are owned by 'dbo'. Each role owns it's own schema - which is the default way the system is defined.
When I create a role - I do not define a schema for that role and no schema is created. Generally, when I add users to a database I set their default schema to an existing schema and assign them to a specific role. In most cases, the default schema is 'dbo' - but I have used other schemas before.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 10, 2010 at 9:30 pm
Jeffrey,
You wrote
>>> I have not found a reason yet to create a schema that was not owned by dbo.
I may have misunderstood what you wrote but every "standard" schema (e.g. db_datareader) in a new database is owned by a fixed database role of the same name. The role , not the schema, is is owned by "dbo". I was curious what the consequences would be of actually making "dbo" (the role) the owner of db_datareader (schema).
Barkingdog
February 10, 2010 at 9:51 pm
On my system, the db_datareader role is owned by 'dbo'. Is this not the same on your system? If not, somebody changed it because all of the built in roles on my system are owned by 'dbo'. Each role owns it's own schema - which is the default way the system is defined.
So, roles are owned by a principal and schemas are owned by a principal. Objects like tables, stored procedures, functions, etc... are not owned by any principal. They are defined within a schema and the schema has an owner.
Once again - if your default system roles are not owned by the dbo principal - then someone has changed the ownership. Now, I am not sure whether or not this has anything to do with your problems or not - but it definitely looks like it could be contributing to any problems you are seeing with permissions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 10, 2010 at 9:57 pm
>>> Once again - if your default system roles are not owned by the dbo principal
My default system roles are owned by dbo. My default schemas are not.
Barkingdog
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply