April 19, 2008 at 12:40 pm
(The problem came about when the developer want to back up their own database).
Because i had owner as UserA
When i clicked on dbo (i could not click on role db backupoperator it said add member failed for database role cannot use special principal dbo) - so i clicked on schema db_backupoperator)
Most of my developers need to have access to the database and want db_owner rights.
because the apps require this
If i set up DATABASEA and then say owner is UserA - i see that the owner will then have dbo which therefore has db_owner rights.
If i leave DATABASEA as sa then create UserA and then in the mappings add DATABASEA and select db_owner as the choices
If i do it this i see UserA as a user in Database
The question is - i do not understand which one should use.
They both worked - i now trying to understand the effect of using the login as the owner.
April 19, 2008 at 2:14 pm
whether dbo or just in db_owner group they will have same permissions within database, BUT see below (cut and pasted from BOL to save me typing)
The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):
Belong to the user creating the object, not dbo.
Are qualified with the name of the user who created the object.
---------------------------------------------------------------------
April 19, 2008 at 5:00 pm
So - what do you feel is the best policy to use
I guess i do not understand
1. If i added user without making it the owner of the database and just right clicked and set db_owner i would see user and could then right click user in the database and add role db_backupoperator
2. If i add user to the owner of database and then right click on dbo i cannot add role db_backupoperator - i have to select schema db_backupoperator.
So its almost one way for dbo user and one way for newuser?
April 21, 2008 at 3:37 am
best to add user to database and add them to db_owner group. there would then be no need to give then any other rights because db_owner would cover it.
be sure they do need db_owner because that gives the users carte balmche on the database (including drop it)
---------------------------------------------------------------------
April 25, 2008 at 11:58 am
You stated that the applications require 'owner' rights. Is that necessarily true, or is that developers being lazy? It seems to me that the application owns the process, not the data. However, if the application also includes all the maintenance tasks - backup job scheduling, disaster recovery, etc, then a case could clearly be made that it's a full cradle-to-grave app and is entitled to ownership.
Otherwise, the developers should learn to live within their limited privileges, and ensure that their application does as well.
Yes? No?
April 26, 2008 at 7:59 am
So do not set the owner to the user....make this say sa.
Add the user then (only give them db_owner if they want to do backups, etc)...otherwise
get them db_datareader, db_datawriter.
They need to be able to create sp, views etc so they have to need db_owner correct.
April 26, 2008 at 5:23 pm
Suppose still trying to understanding. owner vs db_owner
Owner of database (this allows them to do everything like drop db etc).
But adding a user and then giving them db_owner (does not allow this).
I know then owner ---> dbo which is db_owner.
April 27, 2008 at 9:39 am
TRacy.
the difference is
if sysadmin or the database dbo, any objects you create are automatically owned by dbo, if only in the db_owner group they will be owned by the user who created the object UNLESS they specify two part naming convention dbo.object when creating the object.
If you want users outside of db_owner role to be able to create objects you need to specifically grant them those rights (i.e. create table, create view). I would not recommend this in a production database though as you will end up with broken ownership chains. One user (the DBA ) should have control of creating objects in the database
---------------------------------------------------------------------
April 27, 2008 at 11:52 am
That was greatly explained....I got it.
If user is not the dbo they will be mydeveloper.table which is what happens in development then i change this back to dbo.table and tell them they must do create dbo.table.
Some applications do insist of owner of database to be the login/user - i guess the vendors do not do create "dbo".table so thats why you got to use owner ........otherwise it will be created as user.table.
I do believe the penny just dropped.....i just got kind of confused.
Cheers
PS: In Production - no developer allowed not even allowed to read data (only the login necessary to run module is allowed) - so im pretty strict here. Everyone even has roles too...so im good on security just right back at owner i got lost.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply