Sql-server – How to map a Windows group login to the dbo schema in a database

sql serversql-server-2005

I have a database for which I want to restrict access to 3 named individuals. I thought I could do the following:

  1. Create a local Windows group on the database server and add the named individuals to it.
  2. Create a Windows login in SQL Server mapped to the local Windows group.
  3. Map the login to the "dbo" schema in the database, so that the users can access all objects without having to qualify them with the schema name.

When I try to do step 3, I get the following error:

Msg 15353, Level 16, State 1, Line 1
An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys.

I have tried to do this via the IDE, the sp_changedbowner sproc, and the ALTER AUTHORIZATION command, and I get the same error each time.

After searching MSDN and Google, I find that this restriction is by design. Great, that's useful. Can anyone tell me:

  1. Why this restriction exists? It seems very arbitrary.
  2. More importantly, can I accomplish my requirement some other way?

Other info that might be pertinent:

  • The server is fully up to date with service packs and hotfixes.
  • All objects in the database are owned by the "dbo" schema, and it's not feasible to change that.
  • The database is running in compatibility level 80, and it's not feasible to change that to 90 yet.
  • I am free to make any other changes (within reason, depending on what they are).

Best Answer

For why isn't this allowed, read on SQL Server: Windows Groups, default schemas, and other properties. To sum up the article, if one would allow a DEFAULT_SCHEMA to a group, what should be the default schema of a login that belongs to two separate groups, each with its own default? These is a difference between primary identites and secondary ones, and for good reason.

If you want to control the permissions on the 'dbo' schema then just create a login for the local group, then a user in the database for this group and finally grant CONTROL on schema::dbo to this user. This will allow the 3 individuals (and any other user in the local group) full control over anything in the dbo schema. They will be able to alter, select and update any object in the dbo.schema, but they won't be able to create new object (unless explicitly granted). If you want them to have full control over anything in the database, not just the existing objects in dbo schema, then just add the local group user to the 'db_owner' role.

If you want to use schemas as a namespace and save your developers from explicitly using a two part name, then ... just don't. Using a two part name qualifier can do no harm and only adds benefits.

Related Topic