Very Interesting SQL Security Confusion | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Very Interesting SQL Security Confusion

Another interesting incident..We have a login say A , which is Sysadm .
Another login B is a DBO for one database.
Now, if i create a table with A, it rightly has the ownership as "dbo"
BUT if create the same table with B, instead of saying "Object already exists", it is creating the table with ownership as "B".
Now, I understand that SQL server supports the same table name created N times, untill there are N unique owners..But the above case is very confusin…
I expect the ownership of that table created by B, to be "dbo"…
Pls let me know your opinions..
[email protected] "What would you attempt to do if you knew that you could never fail"
-Robert Schuller

BOL refers:

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.
Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
A user who has the dbo role is able to affect dbo objects but can still have their own objects too, whereas a sysadmin has no other identity in the database.<br /><br />a user with dbo role who creates a table will create it under their own name regardless of whether a dbo.table already exists.<br /><br />We always explicitly name our objects dbo. so hadn’t actually encountered this problem before <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Cheers<br />Twan
I’m on my standards kick today. All of our developers are also required to explicitely use dbo. in the create statements. They are also required to configure EM and QA to automatically add it on scripting. Little things save time. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.