SQL Server Performance Forum – Threads Archive

Restriction on dbo

I got stuck into a strange problem.
The scenerio of my database server is such that I create the db ,create login,assign db_owner role to user for the clients programmer
NOw if the programmer create database objects say tables using QA,the owner of table should be the "user" as I havent given him the sysadmin role to his login.
But when I check the ownership of his table I can see dbo instead of his username.
Accord to my knowledge if the person do like this: CREATE TABLE [dbo].[tbl_name]
then only the owner becomes dbo.But I am wondering why clients programmer will do so,as this increases complexity in his procedure or
say to select he have to write select * from dbo.tblname where as if he had created table using his user he hav to simply write select * from tbl_name in his SP or query.
Is there any other way by which ownership has became dbo Moreover any way by which I can restrict from creating table using dbo.tbl_name except for members of sysadmin fixed server role

You can change the object owner using SP_CHANGEOBJECTOWNER and BOL refers: Use sp_changeobjectowner to change the owner of an object if the security account that owns the object has to be dropped but the object must be retained. This procedure removes all existing permissions from the object. You will need to reapply any permissions you want to keep after running sp_changeobjectowner. For this reason, it is recommended that you script out existing permissions before running sp_changeobjectowner. Once ownership of the object has been changed, you may use the script to reapply permissions. You will need to modify the object owner in the permissions script before running. For more information about database scripting, see Documenting and Scripting Databases. Use sp_changedbowner to change the owner of a database. A note about DBO
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. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Is there is any way to restrict creation of tables with user dbo

Not possible DBO is the super user. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>