September 10, 2008 at 9:00 am
Hello All,
I have a question regarding role and schema separation in SQL 2005. A user has sql account
(non windows) and they are part of the public role but when I look at the securable tab it
looks like they elevated permissions on objects. The user with name "A" has a schema also
named "A".
My question is should you still implement role based security by granting the user to the
db_datareader role and then placing him in the data_reader schema or should you just create
a specific schema for that user or use the db_datareader user schema and populate it with
the objects that he needs.
Also wanted to find out how the situation above could have happened, where he is the public
role and then ended up with read, write, update, delete rights on objects.
September 12, 2008 at 2:24 am
Oliver,
I know this can be very confusing for users who are new to SQL 2005 but you cannot add a user the the data_reader schema only grant rights on that schema, but that won't help a lot because normally the shouldn't be any objects in that schema.
Unfortunately SQL 2005 by default creates a schema for each user/role even though you probably won't ever create any objects in these schemas. I usually remove all the schemas related to the default db_roles like datareader. I only leave the dbo, guest and any schema which contain objects. Not sure if that's a best practice but I don't see any reason why I should have schemas which I will never use.
The best practice in your case should be to create the SQL user in the database with a correct default schema. If all your objects are in the dbo schema, then make that the default schema for the user.
Further on add the user to the appropriate roles just like in SQL 2000. In theory you could also grant permissions on schemas directly to the user.
For example if all your tables and views are in the dbo schema and you grant SELECT permission on this schema to the user, you have basically the same permissions as the datareader role. But it's generally accepted that it's better to grant permissions always to roles and not to indiviual users. Especially if you have a lot of database users the use of roles make permission management a lot less work.
Hope this helped you further
[font="Verdana"]Markus Bohse[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply