Home / server / Questions / 92071
Accepted
Christian Hayter
Asked: 2009-12-09 03:06:25 +0800 CST

How can I map a Windows group login to the dbo schema in a database?

  • 772

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

3 Answers

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

    • 4
    1. No clue why it exists. Interesting.

    As for #2, we solve this by creating custom Database Roles for a specific database.

    For example I have 3 domain users who are Developers. I want them to have special access to the database. We create a domain group and put them in it. Then create the associated account in SQL. For that database (Security > Roles > Database Roles) we create a new roll and add that login to the roll.

    The roll can give privledges for specific items within the database, can add in permissions from other roles (such as db_owner) and more. It's quite flexible.

    I've often heard people complain about doing it this way. Mostly because if they have "hundreds" of database objects it takes too much time, but it is quite flexible to quickly add everything or individual objects. Besides it's poor administration not to be particular on the access you're granting.

    • 2

  3. The restriction has to do with not being able to define a default schema for a particular login if it is part of multiple groups.

    Suppose they did allow it.

    If domaingroup DOMAIN\xx has been added to both a local group SERVER\xx1 -> in sqlsecurity mapped to serverlogin 'xx1' in turn mapped to db-login 'xx1' with default schema 'xx1' and local group SERVER\xx2 -> ... with default schema 'xx2'

    Suppose a user of DOMAIN\xx logs in and issues statement 

    CREATE table yy

    Would the table be created in schema xx1 or in schema xx2? Answer=UNDEFINED

    therefore no default schema for groups. however the user can still issue

    CREATE table xx1.yy

    and

    CREATE table xx2.yy
    • 2