How can I map a Windows group login to the dbo schema in a database?
- by Christian Hayter
I have a database for which I want to restrict access to 3 named individuals. I thought I could do the following:
Create a local Windows group on the database server and add the named individuals to it.
Create a Windows login in SQL Server mapped to the local Windows group.
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:
Why this restriction exists? It seems very arbitrary.
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).
