January 20, 2008 at 5:42 pm
Hello All,
As we all know we have something new called Schema in SQL 2k5, and it helps to detach the ownership of the object from the user.
We have schema called Production in Adventuework db , and if I right click the Schema and go to properties and then permission, it lets me add users, db or app roles. Lets say if I add user Joe, give him explicit permission to alter, and delete, can he perform alter, and delete operation in all the objects that are in the schema production.
if not then what are the advantages of adding the users in the schema. what if the user Joe is Db owner? it does not make sense to give him explicit permission.
Any comments will be highly appreciated.
January 20, 2008 at 11:55 pm
I haven't played much with it but I believe you're correct in assuming that the schema is another layer in the hierarchy / tree of permissions. Database -> schema -> table, etc.
January 21, 2008 at 12:01 am
Yes it's no sense for a DB owner but in a database you are some other user and you can specify different right for each, therefore it's easier with the schema to organize the DB right.
January 21, 2008 at 5:52 am
One of the most important things about schemas is that they have taken users out of the object ownership role in the database. This means that a user can always be removed. It also means that user ownership chains are no longer a concern (and they were a big issue in SQL 2000). Schemas can also be used to provide context to queries - an interesting approach to code re-use.
The security model changes relating to schemas are big and cannot all be described here. Take the time to read the books online sections about security and schemas. They are pretty useful and I would recommend using them.
January 21, 2008 at 6:42 am
if not then what are the advantages of adding the users in the schema. what if the user Joe is Db owner? it does not make sense to give him explicit permission.
Remember that the db_owner role is NOT the same thing as "dbo". Just because Joe is db_owner doesn't mean all his objects will be created in with the "dbo" schema. Especially if he doesn't specify what schema he wants to use. In fact, you can create new schemas for each new user in your database if you want.
And if you upgrade a db from 2000 to 2005, all logins will automatically have a new schema created with their names and probably default to those new schemas if they don't specify schemas when creating or altering objects. NOTE: Behavior to watch out for if you don't want your accounts self-mapping to schemas you didn't expect.
You should still map your SQL Logins to a default schema (wish you could do it with windows accounts) and, if the login is not a part of the db_owner role, you should still give add your users to your appropriate schemas & give them the appropriate permissions. It's just a good practice.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply