October 21, 2003 at 7:58 am
I have a user using windows authentication and I have set them as dbo on a database. However, whenever they create anything they are the owner and not as dbo. How do I go about setting this so everything they create is as dbo???
October 21, 2003 at 8:29 am
There's a difference between DBO and DBO privileges.
DBO 'maps' to SYSADMIN privileges.
DBO privileges can be given to any login.
A login needs SYSADMIN privileges to create anything as DBO (it's automatic).
A login with DBO privileges creates anything as the user that created it.
Refer to the Books OnLine (BOL), use the Index tab and enter DBO.
-SQLBill
October 21, 2003 at 8:32 am
Even though they are in 'DBO' database role, If they do not specific 'dbo' as prefix of object when creating objects, The object will be owned by themself.
create table dbo.test ...... will create table "test" belonged to 'dbo'.
October 22, 2003 at 3:21 am
If user fred is a member of SYSADMIN, then any object they create will have a default owner of DBO. Also, fred can explicitly create an object with the name DBO.OBJECT, or ANYTHING.OBJECT
If user fred is not SYSADMIN but has DBO authority, then any object they create will have a default owner of FRED. Also, fred can explicitly create an object with the name DBO.OBJECT, or ANYTHING.OBJECT
If user fred just has db_ddladmin rights, then any object they create will have a default owner of FRED. However, fred can only explicitly create an object with the name FRED.OBJECT
Therefore, if you want a default owner of DBO, the user must be SYSADMIN.
We have a problem with our Remedy system, where the Remedy-supplied maintenance process will create new objects, but does not provide an owner name. We do not want the account that runs this process to have SYSADMIN rights for the rest of its work. Remedy will only work with objects that have DBO ownership, and we cannot amend the maintenance routine, so life is not easy.
We have compromised so that when a schema update is needed, the account is given SYSADMIN for the duration of the schema change and then reverted to DBO authority only. This gets the job done, and keeps Remedy and the security guys happy.
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 22, 2003 at 8:08 am
Making the user the owner of the datbase will make everything they create dbo prefixed. sp_changedbowner which is different than putting a user in the db_owner role will do this.
October 22, 2003 at 10:36 am
you could follow this steps:
1- remove all those users from the DB
2- use sp_addalias 'userlogin', 'dbo'
3- repeat step2 for all those users
Procedures created with those logins will have dbo.procname form!!!!
Note:
- I am assuming sa owns the DB
- when you are done allowing that, make sure you remove the mapping using sp_dropalias
HTH
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply