Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm assinging user to a new database. What is the difference of user being a default dbo user compared to assinging login as a db_owner role member?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Every database should have a dbo user, which by default is assigned to the db_owner database role. There's also a dbo schema, which is partially a holdover from SQL Server 2000 and earlier, where users and schemas were one in the same. It's also slightly special in that queries will look for objects in the dbo schema if they aren't found in the user's default schema (assuming the object name wasn't qualified with a schema name).

You can only map a single login to the dbo user in a database (as is the case with any database user), but you can assign as many users as you want to the db_owner role, giving them essentially full control of the database.

If you set a default schema for a database user, that won't have any effect in terms of security. It simply controls which schema by default will be used to create and reference objects when a specific schema name isn't provided with the object name. In SQL Server 2000 and earlier, since there was no separation of users and schemas, a user's default schema was always itself, unless explicitly specified otherwise, e.g. "CREATE TABLE dbo.test_table..." vs "CREATE TABLE test_table...". (Note that the dbo schema was still used for objects not found in a user's default schema.)

share|improve this answer

Default dbo user cannot view any database objects.db_owner can perform all configuration and maintenance activities on the database

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.