SQL Server Performance Forum – Threads Archive
No login name for dbo of a database
When I go to EM to check the users of a database and the Login Name for the dbo is blank, what does it mean? Thank you.That’s normal. DBO is a special account that acts differently that user accounts you create. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Hi Brad, I believe the Login Name for the dbo will be default to sa.
Thank you.
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. 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.
The dbo user can be blank if you moved the db fom another server and that owner don’t exist on the new server for example. To fix it run: sp_changedbowner ‘sa’
Satya, I want to confirm my understanding about dbo and db_owner. 1. Each database can only have one dbo user.
2. Only one login name can be specified for the dbo user.
3. There can be multiple users belonging to db_owner role of a database. Does the sentence "Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo" means that users associated with logins belonging to the System Administrators server role are automatically aliased to dbo? Thank you.
Argyle, So, if the database is not moved from another server, dbo should always associated with a login name. Does it matter whether which Authentication is being used?
Thank you.
It could probably be blank for some other reason. Set it to ‘sa’ as default if unsure. You can only have one login mapped to ‘dbo’ in the database as far as I know but you can have many logins added to the ‘db_owner’ role. Both will have db_owner rights. One difference is that the login mapped to ‘dbo’ will have all it’s objects created as ‘dbo’, like dbo.MyTable. Say you have a server with a database MyDevDB on it. You have 2 sql logins in the DB. Joe_Developer and Dave_Developer. If you run sp_changedbowner ‘Joe_Developer’ and add Dave_Developer to the db_owner group both will have db_owner rights but this will happen when you create an object: With Joe:
Create MyTable (x int) will be dbo.MyTable With Dave:
Create MyTable (x int) will be Dave_Developer.MyTable For Dave to create objects owned as dbo he would have to type Create dbo.MyTable (x int)
]]>