asked on

What is the purpose of default schemas in SQL?

What is the purpose of default schemas in SQL? I was reviewing list of users in an application and I noticed that for some users I was not able to remove these users from the application. Coincidentally these same users had their name as the default schema name while all the others had a default schema name of dbo. I had to change the default schema name to dbo before I could remove them. What is the connection between default database name and default schema name and how does this come into play with SQL security?

Pavel Celba

The purpose of schemas is to separate database objects to groups having its own permissions. The default schema is used when the application references database object without explicit schema qualification (which is bad practice but still widely used).

Default database name is derived during the connection to SQL Server or by issuing the USE command later. The database name is not used in SQL commands obviously so to have the default database set is rather important in any application accessing SQL Server data. If you don't do it then Master db is used as the default.

Schemas are defined under the database and the default schema is defined at user level. Permissions are also defined under the database. This granularity is not necessary in small in-house applications but imagine large databases with thousands of users...

I cannot answer the question why it was impossible to remove the user when its default schema was same as the user name.

Reading about schemas:
https://www.brentozar.com/archive/2010/05/why-use-schemas/
https://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190387(v=sql.105)
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server

rwheeler23

ASKER

Thanks for these links. I run into this issue all the time. If I go into SQL and try to delete a user that owns a schema I will get a message saying I need to delete the schema first. Are you saying it is bad practice to set the default schema to dbo?

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

Thanks for these tips.

Scott Pletcher

That's not at all a bug. It prevents a serious anomaly in the data.

SQL won't allow you to have a schema whose owner does not exist. That is perfectly logical. In order to delete (drop) a user, you must first delete anything that user owns. That's all SQL is "saying", i.e., that's the internal rule it's enforcing.

Pavel Celba

I've investigated it already...

We have to distinguish between default schema assigned to user (which is mentioned in the question) and schema owner. If the user is schema owner then you cannot delete the user. If the user has any schema assigned as the default schema then it does not prevent you from the user deletion.

The schema may remain unused but dbo user becomes the schema owner in such case.

Assigning the default schema to user does not mean it is "in use". Creating data objects in the schema means the schema is "in use" or assigning the user as the schema owner (which is different from user default schema) means the schema is "in use" by that user and you cannot delete such user.

rwheeler23

ASKER

I am trying to find out how these users are getting assigned as owners of the schemas. When I do a fresh installation of the application the default schema owner is dbo. Somehow over time the default schema ower is changing.