2

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).
1
  • Sorry guys, it's a long time since I asked this question and I had completely forgot about it. Just to give you an update, I have solved the problem by persuading all the developers to qualify every database object with an explicit schema name. It's taken several years to get to this state, but we are finally there. Thanks for all your help. Oct 4, 2012 at 13:31

3 Answers 3

4

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.

1
  • Thanks for your answer, very useful. Unfortunately I do have to use the schema as a namespace, so I will have to do some more investigation. Dec 9, 2009 at 15:32
2
  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.

1
  • Thanks for your answer. However, defining permissions is not my problem, it's mapping the default schema name. Dec 9, 2009 at 15:34
2

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .