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?


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.)