DB Owner

[TMRO]

Hi everybody,

I created a new user in the SQL 2000 in the Security --> Logins area and I assign that user to be db_owner to a specific database.

Now, if I go to any table from the above mentioned database and view the permission for that table, you notice that the newly created user doesn't have any permission on the table.

IS this a bug?

Thx,
TMRO

 

[yelworcm]

Nope. Consider what happens when you add new tables, after you have made the user DBO? If specific permissions are granted at the time you make them DBO, any future object will be left out.

In short, when you access an object, SQL Server checks to see if you are in sysadmin. Failing that, it looks for the next easiest way out (DBO), then for specific permissions.

 

[NoCoolHandle]

In short, when you access an object, SQL Server checks to see if you are in sysadmin. Failing that, it looks for the next easiest way out (DBO), then for specific permissions.

Close but not quite correct.

The first thing sql does is check to see if there is an object owned by the current database user and if not it looks for an object owned by dbo.


The current user however gets the dbo permissions for access to objects inside that db as they are the dbowner role.

I.E.

If I create an table called rob.Table1 and another called dbo.Table1 add a row or two to each (different of course) and then do a "select * from table1", the rows being returned would be from rob.Table1 not dbo.Table1

HTH

Rob

 

[NoCoolHandle]

Oh yeah, and Rob doesn't need any permissions for either table.
Why?
Becuase Rob is in the db_owner role for the dbo.Table1 one. And as a member of db_owner has all permissions for any action with the current database except Restore.
and
even if Rob wasnt a member of db_owner role he would have all permissions (implicitly) for rob.Table1 as he created it.


For what it is worth.

Rob

 

[yelworcm]

I stand corrected ;-)