Unable to set Default Schema for a group

Question

We're using Windows Authentication with our SQL Server.  We've added a domain group to the SQL Server groups and would like to give it a default schema.  The properties window is the same for users and groups but the default schema field is enable only for user entities.  We cannot add a default schema to a group.
 
Is this by design,  a bug or a problem with our SQL Server installation?

Updated on 2005/08/29

My first try was done at through Database\Security\Users.  I tried going through Server\Security\Logins and I got this error message 
 
Alter failed for User 'Domain\Group'. (Microsoft.SqlServer.SMO)
 
Additional Information:
 
--> An exception occured while executing a Transact-Sql statement or batch.
     (microsoft.SqlServer.ConnectionInfo)

     --> The DEFAULT_SCHEMA clause cannot be used with a Windows group or
          with principals mapped to certificates or asymmetric keys.
          (Microsoft SQL Server, Error: 15487)

Why can we not set a Default Schema to a Windows group?  This would be more efficient then to add all the users to the Sql Server logins list.

Answer 1

this is by design. Group cannot have a default schema, since no principal can have group as its primary id (as opposite to group id). Consider following: windows user1 is added to windows group1 and group2. If both of them are allowed to have default schema what default schema user1 has then?

Answer 2

I do understand the situation you described but in a well design group structure, a user will exist in only one group for a specific database.  The group and schema structure will usually be matched.  Otherwise it will become difficult to manage security efficiently.  Therefore it should be possible to assign a Default Schema to a group.
 
The way it is now, if you have 100 users in a specific group instead of having to add only that group with a Default Schema, we now have to manage 100 different users within the database.  If the default schema for these users have to change, there is a high probability that some will be forgotten.
 
I believe Microsoft has a good approach to security by using Domain\Group to which we give privileges and then we had the targeted users to the group.  Why should this approach stop at SQL Server? 

Answer 3

Unfortunately, we cannot design a solution assuming that group structures will be well designed and that users can be members of at most one group in any database. So, in SQL Server 2005, setting a default schema for a group is not supported. The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one.

Thanks
Laurentiu

Answer 4

Regarding this issue, I have a situation:

We have a group of developers that we would want to configure as db_owners of database "B". All this developers belong to network group "A". We associated this group to db_owner role in database "B" and thought it would be enough to configure the desired permission in the db.
The problem is when some developer (belong to group "A") tries to  create a table in the db "B", get's the error: "Property DefaultSchema is not available for Database 'B'. This property may not exist for this object, or may not be retrivable due to insufficient rights. (SQLEditors)".

We tried then to configure the DefaultSchema for the group, but are unable to, because of the behaviour you refered earlier.

So, in this situation, how can I configure a specific network group to be db_owner (or have some other role)? Is it possible in SQL Server 2005? Don't tell me we have to configure all users, one by one...
In SQL Server 7/2000 we have no problem with this.

Tks.

Answer 5

   I found the root cause of the problem you described and it seems to be a bug in SQL Server Management Studio when trying to create a schema-bound object on SQL Server 2005. The tool is internally trying to find (without success) the default schema for the user and failing.

 

   What should happen (to have the same behavior found in SQL Server 2000) is that it should create an implicit user and schema for the caller (let’s call it user1 for simplicity) and the table in the specified schema (notice that the default schema name is the same as user name). It is important to observe that at the end you end up creating an implicit user and a schema for every member of the group that creates an object in the database.

 

   In order to get the same results right now, you have two possible options:

1)      Create the first object using  TSQL directly:

a.       Click on “New Query” and connect to the right server

b.      Write the script to create the table and click on “Execute”, for example:

use [db_Test]

go

CREATE TABLE [MyTable]( data varchar(10) )

go

c.       If you refresh the view in Object explorer you will notice the following changes:

                                                              i.      Table created = [user1].[MyTable]

                                                            ii.      New user = [Domain\user1]

                                                          iii.      New schema = [Domain\user1]

 NOTE:  At this point you can drop this “dummy” table and everything should work fine from now on for this particular user.

2)      Explicitly create a user for [Domain\user1], Schema [Domain\user1] is completely optional

IMPORTANT NOTE: To have an equivalent behavior, you will need to revoke the CONNECT permission on the database to [Domain\user1] (REVOKE CONNECT TO [Domain\user1]), this way the only access to the database would be via the Windows group membership and not by an explicit permission.

 

  I hope we were able to help you, let us know if you have further questions. We really appreciate your feedback.

 

  Thanks a lot,

-Raul Garcia

 SDE/T

 SQL Server Engine

 

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 6

Is MS going to release a fix for this so we can assign a default schema to a group?

 

Answer 7

Hi,

I find this limitation a real pain in the ***.

This should be the same if you couldn't set a default db any more for a windows group. Because you have the same problem in this case if a user is a member of 2 different windows group and those both groups do have a login into SQL with 2 different default databases.

I know that you're asking for trouble at that moment. But I know that this is a situation to avoid.

The common sense need to be followed with the default schema on windows groups. You know the consequences if you don't make proper use of it.

I really hope that whit SP1 we can set a default schema to a windows group.

 Greetings

 

Ludo

 

Answer 8

I'm glad to see that my view on this issue is not unique.  I previously file a request to include this possibility in a futur release and it was also turned down.

 I believe the need is real and Microsoft should come up with a solution.  I understand their point but also that it coul be overcome by putting in the proper validation and dialog boxes scenario.  The people at Microsoft were able to upgrade Sql Server from "acceptable" to "great", but I believe that there is still place for improvements and the possibility to assign a default schema to windows group is one of them.

Keep adding your comments and we might be able to turn this around.

Thanks!

Answer 9

This issue is already on our radar. It is actively tracked; we are not ignoring it. For your reference, the item that tracks this is 299032.

Thanks
Laurentiu

 

Answer 10

Thanks Laurentiu, this is good news.

Gilles

Answer 11

I glad to hear this.

This is very good news.

I hop it will be afvailable in SP1 or with a seperated hotfix.

Greetings

Ludo

Answer 12

I agree this is a must add feature. We have a group of developers all developing the same database. We want to grant the AD group that they all belong to all necessary permissions and set their default schema to dbo. As it stands, we cannot set the default schema and have to create individual accounts for all of them.

***This negates the whole convenience of granting granular db permissions to the AD group.***

Please fix this as it is very frustrating.

Answer 13

Indeed!!

Answer 14

Can you please provide a link to the MS item number that you reference above?

Answer 15

All,

This issue is probably dead, but I just discovered something new today that is relevant to it. 

I've got SP1 installed on my workstation and we're testing our new servers by migrating our current SQL Server 2000 db through Dev/Test/QC and Production SQL Server 2005 on the new machines.  While I am completely unable to manually assign default schemas to NT User Groups as I add the Groups into SSMS, when the database converts from 2000 to 2005, it AUTOMATICALLY adds in a new default schema for each individual user that is already under the database - > Security -> Users tab regardless of login type.

Essentially, it doesn't matter if the login is a SQL login or an NT login.  If the users are in the database's sysUsers table, they get a default schema named after them during the migration and then you can see the default schemas in SSMS under Database -> Schemas.

So, if it's so hard to patch the software so DBAs can manually set up default schemas for NT Users/User Groups, then why is SQL Server 2005 automatically doing it during a conversion?  And why can't the code doing it here be used for creating the patch?

This post is not intended to be a flame.  I seriously want to know why there's such a disconnect between what can manually be done and what happens automatically.  Thank you in advance for help me understand this issue.

Catadmin

Answer 16

Note that the schemas you see added for groups are not default schemas for those groups.

The schemas are created automatically on upgrade for backward compatibility. In SQL Server 2000, schemas and users were overlapping - there were no real schemas, but it looked as if every user had a schema named after him. To keep things working, in SQL Server 2005, creating a user using SQL Server 2000 syntax will also get a schema created. Compare sp_adduser with CREATE USER: sp_adduser will create the user and a schema. CREATE USER will only create the user.

Thanks
Laurentiu

Answer 17

Agreed!

The problem I found was that I could map the group without using a Schema, set them as "db_owner"'s,  but when developers tried creating a table they got a  DefaultSchema not found type error! 

Barkingdog.

 

Answer 18

So, from the Administrator's perspective, what is the best way to handle this awkard situation?

For "ordinary" users I plan to create AD groups and assign the group a database role (but no schema of course). Given the users will have no schema, I believe that each user will default to the "dbo" schema.  (Then we need to harcode all table names, stored proc names, etc with the "dbo." prefix. Better review the old code...)

Any thoughts on this much appreciated!

 

Barkingdog

Answer 19

Actually, you're wrong.  We had lots of problems because these users did have the schemas set up as default schemas in the database.  I had to specifically drop several schemas because it was causing us issues in our upgrade.  Additionally, I then had to go in and recreate stored procedures to use the "Create dbo.<myobject>" format instead of the original "Create <myobject>" format used on some of our older procs.

Again, any information on why this happens would be appreciated.

 

Thanks,

 

Answer 20

How did you determine that those schemas acted as default schemas? Can you provide a sample script or describe your scenario in more detail?

Thanks
Laurentiu

Answer 21

I was under the impression that a login or group was defaulted to dbo unless specified.
 
So far the two larger issues I've seen are that the user under this group (all server roles except serveradmin/sysadmin) can not open a table in design mode, nor view the properties of an object (sproc, udf, view, etc.).

Is anyone aware of a workaround? Does Microsoft plan on addressing this?

Thanks

Answer 22

Any update on this issue? Will it be on SP2?

Answer 23

There is mention of an item 299032 this is tracking this issue.  How does one find item 299032?

Answer 24

It's an item in an internal database. If you contact customer support about this issue, you can give them this number to get context about the issue. I don't think you can use it otherwise.

Thanks
Laurentiu

Answer 25

I have one question and a couple of recommendations regarding Default Schema's in SQL Server 2005 ...

I've been researching the notification message [Property DefaultSchema is not available for Database [DatabaseName]].  This message is generated when I try to create a new table object using SSMS.  I am logging into the SQL Server using an NT Authenticated Login which is mapped to an Active Directory Resource Group.  I have found the following information specific to Default Schema's in SQL Server 2005.

First, by design, you cannot assign a Default Schema to an NT Authenticate Login that is mapped to a Windows Group.  This is noted in the CREATE USER (Transact SQL) BOL topic - http://msdn2.microsoft.com/en-us/library/ms173463.aspx 

"DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a Windows group, a certificate, or an asymmetric key."

QUESTION 1: 

Are there any plans to remove, or modify the query used to derive the Default Schema which is generating the message notification from SSMS?

SELECT (select default_schema_name from sys.database_principals where name = user_name()) AS [DefaultSchema]

This is quite frustrating for me, as I have to reply to my developers - by design you will receive this message when you try to create a table through the table editor using SSMS.  I have also been informed that this same error is raised through VSTS for Database Developers. 

In the following post, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1 this has been noted as well, and Gilles L. notes that "Group cannot have a default schema, since no principal can have group as its primary id (as opposite to group id). Consider following: windows user1 is added to windows group1 and group2. If both of them are allowed to have default schema what default schema user1 has then?"

RECOMMENDATION 1:

I would recommend that this behavior be modified to assume that if by chance a principal is assigned to multiple Resource Groups, of which map to NT Auth. Logins/Users in the same database, that the level of permissions are derived based on an order of precedence.  I would also recommend that this order is implied based on the least amount of permissions assigned.  Therefore, if DOMAIN\USER was assigned to DOMAIN\RSC_GRP01 and DOMAIN\RSC_GRP02, where RSC_GRP01 was assigned as db_owner and RSC_GRP02 is assigned as db_datareader that the permissions would be granted only as db_datareader ... DOMAIN\USER will complain, and group assignments will be corrected.

RECOMMENDATION 2:

Okay, so maybe RECOMMENDATION 1 is too difficult - instead, of checking for a default schema, check for multiple group assignments on the SQL Server-side, and generate an error when this exception to the rule occurs.

 

Answer 26

For the first question, you should post it on the SQL Server Tools Forum, as it is related to SSMS.

For the other suggestions, recommendation 1 is not very precise - how would you exactly define the order of precedence of sets of permissions; in other words, how would you be able to determine if a set of permissions is of a higher level than another? What if the groups are not assigned to any roles? The recommendation raises more issues than it resolves. Recommendation 2 is ok, but usability isn't very good - you might have been able to connect to the database, but if your group membership changes, you'll suddenly find out that your connection fails.

Also, please make any future suggestions on the original thread that you mentioned, so that we can keep track of them easily.

I'll append this thread to the original one.

Thanks
Laurentiu

 

Answer 27

Today I installed SP2. There are no changes, the problem is not solved. Through SSMS is also still not possible to create tables or views. It would be interesting to know if Microsoft will implement a solution for the final release of SP2

Regards, Moreno

Answer 28

Bringing topic back up to the top ... this is quite a problem in my environment. I end up giving to many developers admin rights when I shouldn't have to be.

Thanks

Answer 29

Any update on this topic?

Answer 30

There are no updates yet.

What is the reason why you are granting admin rights to your developers?

Have you considered the following?

 - use a group as usual, to grant access to the server and to the database
 - create a user without a login in the database (see CREATE USER ... WITHOUT LOGIN), with a default schema assigned to it. The permissions needed by developers in the database can be granted to this login.
 - grant permission to impersonate the loginless user to the group
 - whenever a developer connects to the database, have him impersonate the loginless user - he will get the permissions and default schema assigned to that user

The above steps assume the work you need to do is limited to the database, hence I suggested using a loginless user. However, if you need to do work across several databases, this can be extended to impersonating a login instead of a user. Several variations on this theme are possible.

Thanks
Laurentiu

Answer 31

Hi, Laurentius, thanks for the update.

May I suggest something? Currently, a default schema cannot be assigned to Windows groups because there is no way of SQL Server choosing which schema to use in case a user belongs to more than one group. Thus, Windows groups have a NULL default schema, which in practice means that the dbo schema is used as the default, correct?

What if SQL Server 2005 allowed default schemas to be assigned to Windows groups and used the dbo schema for resolving conflicts? In this way, if the user connecting to the database belongs to only one Windows group that has access to that db (which I believe to be the most common scenario) the default schema of that group will be used.

On the other hand, if the user belongs to more than one group in the db, causing a default schema conflict, the dbo schema is used as the default, which is similar to the current behaviour.

This would cater for most of our needs, I believe, providing a reasonable way of managing default schemas for users in Windows groups without having to create those users individually in the database.

Regards,

André

Answer 32

The solution you suggested doesn't resolve the fact that one day you may be able to create objects in schema X, and then the next day, due to another of your groups being assigned a default schema, you would end up creating objects in schema dbo. It is this kind of nondeterminism that we want to avoid.

Thanks
Laurentiu

Answer 33

I see your point; but how does it currenly work for default db and default language properties?

SQL Server seems to arbitrarily choose the db and lang defaults of one of the existing groups the user belongs to. If you add a new group with a different default db and default language (or delete one of the existing ones), the next time the user connects it may be working with different connection properties. This sounds like the "nondeterminism" you said MS is trying to avoid.

It just doesn't seem to be consistent.

Regards,

André

 

Answer 34

It's inconsistent, I agree. But it is a legacy issue - we can't take that back now. Consistency cannot justify making a bad design decision. And using the wrong default schema without knowing it can have significant security implications.

Thanks
Laurentiu

Answer 35

Hi Laurentiu, et al,

This approach, and its derivatives and siblings, is particularly painful for me, not just from a technological perspective, but from a business perspective, in the sense that the only workarounds force an intersection between two distinct branches in the IT structure:  Security and Operations. 

From an operational perspective DBA’s should not have to worry about new users needing database user access on a case by case basis (particularly when it is the SSMS and not the engine); our task is to provision policy/group based security that can then be assigned to individuals via the CISO’s office and the security staff.  We generally focus more on availability, reliability, serviceability, integration, performance tuning, recoverability, physical data representation, operational security, and resource utilization:  some of the things that make the difference between enterprise class database environments and business class environments.  Consider us spending our day with email saying:  please grant access to user DOMAIN\Principle for the Foobar database.  Now consider very large n for those emails. 

From a security perspective, the suggested workarounds force security staff to understand operational database techniques since they must implement security based operational changes on a case by case basis for each user; their task is not to understand the database, but to understand what groups have what access to what data/accessors in the database, and then grant/revoke access based on policy, procedure, and compliance.  Consider a new version of SQL Server, like the step from 2000 to 2005:  the CISO's staff would now have to learn the operational features of 2005 in order to do their job (principles and schemas)?  Furthermore, consider a liquid IT environment where a major paradigm shift replaces SQL with another RDBMS.  Does the security staff now have to relearn a new tool or language?  I may have to, should they?

Notwithstanding that, there is the security and operational management inefficiency introduced when having to create individual “login-less” users as to alleviate the problem.  Essentially, this defeats part of the purpose of an integrated MSFT enterprise (group/policy based security, delegation, integration, manageability, etc…) when it comes to SQL Server 2005.  Alternatively, this could be done by some sort of provisioning tool with a framework for scripting against the DB and AD (think MIIS), or even with a custom application.  But, this is not the promise of the MSFT enterprise; nor is this limitation an appropriate additional cost for an enterprise as the result of using SQL, AD, and the integrated MSFT environment.

Lastly, the fact that design considerations, suggested solutions, and workarounds take into account the little things like determinism, ;), could perhaps be the reason why we are asking you to look at this.  It is a pain point for us and you, but we come to expect more as I am sure you do. 

Thanks for the careful answers,

--Robert

Answer 36

Have you considered the following?

.
.
.

Thanks
Laurentiu

-------------------------------------

I am intrigued by this approach, but I need more information on the details of getting it to work.
Can someone point me to a blog / site that lays out, step by step how to get this up and running. I have tried to get it going myself, but to no avail.

Thanks

JT

Answer 37

Re: Robert J. Fortunato Jr.'s post

I understand your request. I have also closed the item through which we were tracking this issue originally, in favor of activating a more recent customer report that you can access at: https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&wa=wsignin1.0&siteid=68. Comments made in that report will be available to anyone that accesses our internal request database. I also included a link to this thread for reference.

Thanks
Laurentiu

Answer 38

JT,

Can you provide more information on what you attempted and where you couldn't make things work. The steps I mentioned above should be pretty easy to follow.

Thanks
Laurentiu

Answer 39

The last step is the part I am most unsure about. Specifically

whenever a developer connects to the database, have him impersonate the loginless user - he will get the permissions and default schema assigned to that user.

How exactly do I have the developer impersonate the loginless user? Do they log in under their own name and the impersonation just happens? Or do they log in under the loginless user and it uses the impersonation to figure out if they have access?

And since I am using multiple databases, I was hoping to set up some logins and use impersonation for those. Any details on how this is done.? 

I am betting that we just set up a SQL Login, give them dbo permissions in the appropriate databases, then the developers log-in as that user.

My issue with this approach is that it is very unsecure, a pain in the butt to maintain, and it really defeats the whole purpose of setting up groups in AD.  (I mean what does it buy us to get the devlopers into AD groups if we turn right around and tell them to login using a SQL ID?)

Finally - are these logins to be SQL logins, or can I use Windows Authentication in your scenario. SQL logins are out at our organization, unfortunately.......

Thanks

JT

Answer 40

Depending on how the connection is done, this impersonation step can be done by the application connecting, or by the user itself - manually.

The idea is to connect as normal, but when working in the database, to issue an EXECUTE AS USER = 'loginlessuser' statement. When work in the database is complete, you can issue a REVERT statement.

You don't need to give dbo permissions to this user - just assign it the minimum permissions required.

There are no additional logins involved in this scenario - you still connect as usual, via your Windows group membership, for example. The user is loginless, meaning that there is no login linked to it.

If you're setting up groups in AD just for the purpose of managing permissions in SQL Server, you're doing the wrong thing - you should use roles instead. Roles and groups can both be used for managing permissions and having them apply to all group/role members. If you already have a group that you can use, it is convenient to use it for this purpose, but you should not create it for this purpose.

Roles and groups are containers of principals, to which you can assign permissions, so that each principal inherits it via his membership in the group/role. Principal properties such as the default schema cannot be assigned to these entities and have to be assigned individually. Groups and roles cannot be used for setting individual properties. The reason for this is that it is ok to have multiple permissions from multiple sources - no conflict arises in this case, but if you have multiple default properties from multiple sources, you have a conflict in deciding which one to use.

We understand the manageability issue raised in this thread. It can be addressed and we have various solutions that we are exploring. But they are not as simple as having schemas assigned to groups.

Thanks
Laurentiu

Thanks
Laurentiu

Answer 41

  Yesterday I was discussing this particular issue with a couple of people during an event, and we found out that the main reason, at least in their case and hopefully in most of the cases, is to allow proper operations on the schema (i.e. create objects, select, etc.) and not really for name resolution.

 

  Default database is really a property used to allow name resolution in the case someone references an object without specifying the schema, but it has nothing to do with permissions.

 

  I suggested the following:

·         Create the Windows group as usual

·         Create a schema, and either:

o   GRANT only the minimum permissions on the schema to the Windows Group

o   Make the Windows Group the owner of the schema

·         For any object reference, always use the schema name explicitly instead of relying on default DB

 

  The people who asked the question liked the idea as it seems to be a good solution for their particular scenario, and they also suggested sharing it on the forum, hoping to help other people who face similar problems. I also include a small demo I hope will be useful.

 

-- Create Windows group

--

CREATE USER [DOMAIN\WindowsGroup]

go

 

-- Create a schema owned by the Windows group

-- Notice that it is not the default schema for anyone,

-- but any member of the Windows group will have ownership on it

--

CREATE SCHEMA [ApplicationSchema] AUTHORIZATION [DOMAIN\WindowsGroup]

go

 

-- As an alternative, you can create a schema owned by someone else

-- and just grant minimum permission on it to the Windows group

-- This way any member of the group will have the right set of perms

-- on the schema

--

CREATE SCHEMA [DataSchema]

go

GRANT SELECT ON SCHEMA::[DataSchema] TO [DOMAIN\WindowsGroup]

go

 

-- Sample tables

CREATE TABLE [DataSchema].[Demo] ( data nvarchar(100) )

INSERT INTO [DataSchema].[Demo] VALUES ( N'My data' )

go

CREATE TABLE [ApplicationSchema].[Demo2] ( data nvarchar(100) )

INSERT INTO [ApplicationSchema].[Demo2] VALUES ( N'My data2' )

go

 

-- For the example, I will grant create table & Procs on the group as well

--

GRANT CREATE TABLE TO [DOMAIN\WindowsGroup]

GRANT CREATE PROCEDURE TO [DOMAIN\WindowsGroup]

go

 

-- Testing as a Windows group member!

-- WindowsUser is a member of WindowsGroup

--

EXECUTE AS USER = 'DOMAIN\WindowsUser'

go

 

-- Look at the token just to see the group membership

SELECT * FROM sys.user_token WHERE name = 'DOMAIN\WindowsGroup'

go

 

-- Try to create a procedure on the Applications schema

-- as the group has ownership on the schema, and permission

-- to create procedures, it will succeed

--

CREATE PROC [ApplicationSchema].[sp_Demo01]

AS

  SELECT * FROM [ApplicationSchema].[Demo2]

  -- The Windows group have explicit permission to select on this schema

  -- This call should also succeed!

  SELECT * FROM [DataSchema].[Demo]

go

 

-- Execution will succeed as expected

--

EXEC [ApplicationSchema].[sp_Demo01]

go

 

REVERT

go

  

I would also like to suggest that if this particular solution doesn’t work for you, to let us know why and what is the particular scenario/problem you are facing. If we can understand your problem we may be able to recommend an alternative using the current infrastructure in SQL Server.

 

  Thanks a lot,

-Raul Garcia

  SDE/T

  SQL Server Engine

Answer 42

It is very frustrating for a windows group member to have aspects of the Managment Studio GUI (create a table) rendered useless.  It is even more frustrating  to violate "best practices" and grant individual rights/permissions just to get the GUI to work.  The MS DBA community is not represented by a single expert skill level and relies heavily on the tools provided by MS. Please get the default schema/windows group issue resolved in the GUI.

Answer 43

We would of like to use the mecanism of default schema (as describe in BOL) to  define several developers environments within the same database.  That is based on the fact that if a table doesn't exist within the default schema used, the table will be look for under the DBO schema.  This would of allowed us to create all of our tables under the DBO schema and only the ones required for updates under the developers team schema.  This allows for portable SQL from development to integration and to production environments.  For example,  SELECT * FROM ABC would always work by looking at the default schema first then at the DBO schema.  It also helps to  save on disk space.

Since the developers in the same team are all within a domain group.  Being able to use a default schema for a domain group is the way to go for us.

Any other solutions is a patch that only answer specific needs.

Gilles

Answer 44

Laurentiu Cristofor wrote:

- create a user without a login in the database (see CREATE USER ... WITHOUT LOGIN), with a default schema assigned to it.

Laurentiu Cristofor wrote:

The idea is to connect as normal, but when working in the database, to issue an EXECUTE AS USER = 'loginlessuser' statement. When work in the database is complete, you can issue a REVERT statement.

Let me see If I understand this correctly ...
- Create a user with no binding to a login
- Create ROLES and bind those to USERS
- Said user connects to the server, being part of an NT group with no access
- Does all work via EXECUTE AS USER (user that is bound to the roles)
?

Having to run EXECUTE AS USER for every create seems quite ridiculous when this wasn't a problem in 2000. I'd prefer to continue to use our current work around in by simply using dbo. in the front of all creates. This is still only a work around for TSQL ... we have many a developers that prefer to use the GUI. I'm amazed at how long this thread has gone and the issue with the GUI has yet to be resolved.

Laurentiu Cristofor wrote:

If you're setting up groups in AD just for the purpose of managing permissions in SQL Server, you're doing the wrong thing - you should use roles instead. Roles and groups can both be used for managing permissions and having them apply to all group/role members.

In our environment we have about 40 or so regular SQL developers/users and about 60 SQL servers. By far the easiest way to manage this many permissions was to create AD groups per SQL Server. We then use a front end tool to communicate with active directory to put users in groups. The groups are added to each server by server name and appropriate permissions are added per group and refreshed daily. Absolutely fantastic concept and was working like a champ in 2000. Now 2005 comes along and "working as intended" seems to have caused more trouble and has only caused more problems.

I have yet to see a real solution here ... I have seen some good ideas when using schema's, but in our environment we do not, nor plan on using anything outside of dbo. Life would be quite good if groups could default to dbo ... I don't understand why it's that difficult to allow for this. You actually cause many more problems by defaulting group users to their credentials supplied in by causing backslashes in the schema owner.

Any help is appreciated.

Answer 45

AAraújo wrote:

Hi, Laurentius, thanks for the update. May I suggest something? Currently, a default schema cannot be assigned to Windows groups because there is no way of SQL Server choosing which schema to use in case a user belongs to more than one group. Thus, Windows groups have a NULL default schema, which in practice means that the dbo schema is used as the default, correct? What if SQL Server 2005 allowed default schemas to be assigned to Windows groups and used the dbo schema for resolving conflicts? In this way, if the user connecting to the database belongs to only one Windows group that has access to that db (which I believe to be the most common scenario) the default schema of that group will be used. On the other hand, if the user belongs to more than one group in the db, causing a default schema conflict, the dbo schema is used as the default, which is similar to the current behaviour. This would cater for most of our needs, I believe, providing a reasonable way of managing default schemas for users in Windows groups without having to create those users individually in the database. Regards, André

This is exactly what needs to happen ... default to .dbo!

Answer 46

 

"Having to run EXECUTE AS USER for every create seems quite ridiculous"

 

It is indeed ridiculous, but I wasn't suggesting that. I was simply suggesting doing this once per session. If you run a single query per session, then indeed my suggestion won't help.

 

"By far the easiest way to manage this many permissions was to create AD groups per SQL Server."

 

I don't think this is easier than managing roles, but it is anyway a secondary point in this thread, as neither roles nor groups can be assigned default schemas. My observation was simply that it would be better to manage permissions via roles, because using Windows entities just for managing SQL Server permissions is not a good practice.

 

"I'm amazed at how long this thread has gone and the issue with the GUI has yet to be resolved."

 

The issue in this thread is not a GUI issue. The GUI will not allow you to set a default schema for a group because the database doesn't allow that. You can vote on this issue at https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&wa=wsignin1.0&siteid=68 to bump the relevance of this item.

 

"Life would be quite good if groups could default to dbo ... I don't understand why it's that difficult to allow for this. You actually cause many more problems by defaulting group users to their credentials supplied in by causing backslashes in the schema owner."

 

Groups don't default to dbo, because groups don't have any permissions by default on the dbo schema. The default schema can be overwritten by explicitly specifying a schema.

 

Thanks

Laurentiu

Answer 47

Laurentiu, are you saying that at this present moment in time Microsoft would rather have 156 individual logins on each database with permissions managed inidividually rather than through 3 windows groups? At the moment, that is the route you are forcing us down. How can Microsoft expect me to recomend a migration to SQL Server 2005 from 2000 knowing that this will make administration a nightmare?

 

If this is an issue trying to assign a default schema to a user attempting to create an object, why not allow the user to select or specify a schema that they wish to use, just as you can explicitly specify the schema when you are creating an object using TSQL? If the schema doesn't exist or the user doesn't have the permission to create a schema then you get an error message just as you would when using TSQL. This avoids the need to specify a default schema for a windows group and all the problems associated with it, it also has exactly the same security as if you were using TSQL.

Answer 48

No, I have not stated any preferences, personal or otherwise. I am merely explaining why some proposed solutions are not acceptable and what some workarounds could be in the meantime. This continues to be an open issue and this is why we still have a feedback item tracking it.

 

Also, this thread does not cover any Management Studio issues, hence I cannot comment on why the schema cannot be specified in the UI or the status of that request - this is something that you should inquire about in the Tools forum.

 

Thanks

Laurentiu

Answer 49

Hello Laurentiu,

If you'll remember I posted several weeks ago, and then followed up with a support ticket and escalation route through my TAM.  I believe the major pain point for most DBA's is going to be the SSMS's irritating behavior for the DefaultSchema property.  There are Management Studio issues at play that can be resolved by tuning permissions to satisfy the GUI's poor behaviors without implementing user login based workarounds throughout the databases.  Deterministic default schemas for group based security within the engine are indeed another issue.  You should reference the support ticket I escalated or contact my TAM (email me if you need the information please) as we have been able to relieve some of the GUI problems without implementing user based workarounds.  The specifics involved Service Pack 2 for the client tools, VIEW DEFINITION permission for the group, and whatever set of CREATE/ALTER/DROP or other DDL commands DBA's are attempting to assign to the group.  The Program Manager for the Manageability & Servicing Platform was also involved in coming up with the workaround.  He also might be able to provide you and the community with some relief via the SSMS.

As far as the engine and default schemas are concerned I think this thread is going to go for a while.  Take care, and thanks again for all of your support. 

--Robert

Answer 50

This error occurs within SQL Server 2005 Management Studio, not in TSQL. Does this topic only cover Visual Studio?

The UI behavioural comment was a suggestion, but I understand why you can't take that on board.

Can you give me a solution for managing large numbers of users with this issue?

Answer 51

The security forum is mainly useful in addressing issues related to database engine security features. Most Microsoft employees monitoring this forum work in the security team and can provide advice for issues related to core interfaces such as T-SQL DDL commands, server builtin functions, and security system catalogs. Higher level interfaces such as those provided via SMO or the Management Studio GUI are managed by different teams that monitor different forums. So, it helps to post in the right forum so that you can get direct access to the owners of each feature - with a little luck you can get to exchange messages with the actual person that will make the code changes that will answer your request. This is why I recommend posting any Management Studio specific request to the Tools forum. For errors that are Visual Studio specific, you could start by posting in a Visual Studio forum.

 

Thanks

Laurentiu

Answer 52

No offense intended Laurentiu, but this forum has been going since August '05 and nothing has been done to fix the issue nor an acceptable work around has been developed. This is an issue that should no longer be discussed in this forum or any another by the users. Instead the developers need to come up with a proper fix as you can see this is quite an issue for many users. This thread may only have 50 posts, but it has 15,000+ views meaning that there are many others out there searching for an answer to a problem that should no longer be present. Several posters here have given very well written and thought out solutions, several of which are acceptable and should be looked into by the developers. Instead here we are 2+ years later being told to hope that some random developer will come along and catch wind of this and maybe if we're lucky we can see a fix in the future.

You need to understand that this is quite the inconvenience for the administrators out there. Whether it's this forum, that forum, or some other forum, this all relates to SQL Server 2005 security regardless. This is a problem and we would like some reassurance that something will be done about it.

Thank you

Answer 53

The feedback item at https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&wa=wsignin1.0&siteid=68 has been closed by mistake. We'll look into reactivating it or opening a different one so that you can vote for the relevance of this issue. Views of this thread are not a good measure of the relevance of the issue, as I alone have probably contributed to about a hundred views. The solutions discussed here so far are either not acceptable or are acceptable only for a future release.

 

Thanks

Laurentiu

Answer 54

I agree Laurentiu, there are no solutions here or from Microsoft that have been helpful to an issue that has been on-going for 2 years. With no offence intended to anyone here of course.

Thanks,
Phil

Answer 55

I don't see why the management studio cannot default to dbo schema when it can't find a default schema for a user - that seems intuitive. Similarly, if a user creates a table without specifying a schema, SQL Server shouldn't create a schema automatically - that's causing us all sorts of problems. If the schema doesn't exist it should either default to dbo, or error.

If the user doesn't have access to the dbo schema, then obviously an error would occur. This solution wouldn't be hard, and I don't believe it compromises security in any way.

Answer 56

So I've just trawled through 4 pages of posts to determine that there is no fix and there doesn't appear as if there is going to be one.

 

I am truly flabbergasted, as I have exactly the same problem and thoughts as virtually all the respondents.

 

Can anyone condense this topic down to produce a reliable workaround?  I assume by my quick scan, that the solution if you use the GUI is to set up each user likely to be creating a table to have an individual login or to give the group sysadmin (don't like that).  Our users are research staff who need to create tables etc for general analysis etc, and I'm sure they won;t be too pleased to use the TSQL route (Incidentally, I have resolved the problem of them forgetting to create tables, sps as dbo by putting in place a trigger).

 

Ian

Answer 57

You can not say that this is "by design" when the resulting behavior makes "deny create schema" break. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1960508&SiteID=1&mode=1.

Consider this: a Widnows user can clearly be a member of multiple Windows groups. I can create logins for each of those multiple groups in SQL Server. Each of those logins can be mapped to the same database as different users. Each of those logins can also have different default databases and default languages. Somehow, you can resolve these sort of ambiguities yet are unable to resolve a group having a default schema? Either all such issues should not be allowed, or all should be allowed. You can't have it both ways since this is clearly not consistent.

Yes, you have created a dilemma here, but you can't pretend it does not exist. At the very least, make the default behaviour that users who do not have a default schema must always specify a schema for new objects. That's what the English language word "default" means, after all. You don't have a default, then I can't infer one for you so you have to qualify object names.

Answer 58

I have been trying this approach - what I find is that a group member can impersonate the created user and create databases VIA CODE that will belong to the dbo schema, but they still can't user Management Studio to create a new table - the error message is the same.

Answer 59

Like a lot of developpers, I have the same problem, in Management Studio, I can't create a view or table using the context menu, "New Table..." or "New View...", it display me the f**** message about the DefaultSchema property.

I don't understand why it doesn't works in Management Studio and works well in T-SQL. Is my developpers needs to create the view or table by code and then, use the user friendly GUI to add their columns ?

My dba don't want to put us in sysadmin group, so what can we do ? Rollback to Sql 2000 ?

And where is Laurentiu ?

 

Ze Seb, desperate developper

Answer 60

After reading all the posts I'm still trying to understand how come that if user1 is member of NT group 'MyDomain\DBA' and this group is define as SysAdmin server role, when user1 connect and create a table there is no schema problem and the table is craeted as DBO while if this group was only db_owner on the DB it will fail with the error "Property DefaultSchema is not available for Database"? why sysadmin is mapped to dbo and is there any permission i can grant on the server level to MyDomain\DBA that will allow that without giving them the sysadmin role?

 

Guy.

Answer 61

Laurentiu Cristofor wrote:

The feedback item at https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&wa=wsignin1.0&siteid=68 has been closed by mistake. We'll look into reactivating it or opening a different one so that you can vote for the relevance of this issue. Views of this thread are not a good measure of the relevance of the issue, as I alone have probably contributed to about a hundred views. The solutions discussed here so far are either not acceptable or are acceptable only for a future release.   Thanks Laurentiu

Type	Bug	ID	238246
Status	Closed (Fixed)	Access Restriction	Public
Opened By	LawrenceLau	Blocking Issue	No
Opened	11/15/2006	Submission Language	English
Resolved	7/30/2007 5:04:06 PM	Closed	7/30/2007 5:04:08 PM

Been a few months, feedback link is still closed.

Been 26 months on this case, no progress has been made.

What else can we do to have something be done about this?

Answer 62

So, any updates?

Will this be resolved in 2008?

Answer 63

I don't have any updates on this, just a few comments and suggestions:

 

Comments:

 

 - The best way to get traction on the issues discussed on this thread is via feedback items. Unfortunately, the existing one got closed by mistake (during a migration of the bug database) and this means that that item doesn't get tracked by anyone internally, regardless of whether you comment on it or not. I tried to reactivate it but it is still registered as closed.

 - When pursuing these issues, it is important to not bundle them together. GUI issues and database engine issues should be tracked separately - preferably in different threads and by different feedback requests, because they would be resolved by different teams/people. GUI issues should be posted on the SQL Server Tools General forum, not here. You can link the threads by posting their URLs in the main post in a "Related discussions" section.

 - The fact that the feedback item is closed doesn't mean the SQL Server team has forgot this issue - it just means that there is no feedback loop involving you - the customer. This forum is not the appropriate tool for tracking open issues - it can only help for initial inquiries and reports. The feedback site should be used for tracking open issues. If an item gets closed by mistake, like this one had, you should just open another one. This is the only item I know of that was closed by mistake, so I don't expect this mishap to recur.

 - The reason why the feedback site is better than this forum is because it results in an internal customer report that allows you to get feedback from the persons in the product team that are working on addressing it. It also allows people that look at the report to see how many customers care about it. You can reach more people this way than you can reach via this forum. And the feedback you provide in the feedback items will directly help determine the priority assigned to solving the problem; thread comments have no such effect.

 - Keep in mind that each reported issue is compared to many others, to determine its priority. This issue has received a lot of comments on this thread (it's probably the longest thread here), but it has only been rated by 17 users at this time - much fewer ratings than postings and negligible compared to the number of views. Even if the item would be open, it wouldn't be seen as high priority with just 17 ratings. Customer feedback is one of the most important aspects in determining whether a new feature should be added or not - the way we determine it is based on numbers of requests - we don't have a process through which we can count forum comments - so those don't help.

 - I have moved out of the SQL Server organization almost a year ago, so I can't give you updates on this issue. I'm still checking this forum from time to time, but I am no longer aware of current development plans, so I cannot comment on plans for this issue.

 

Suggestion:

 

To get feedback on the issues discussed here, I suggest starting separate threads for each separate issue. But more important than starting threads is opening a separate feedback report for each issue and making sure it stays open. Commenting on those items and especially voting on them is more important than commenting in the forums - the forums can only help with discussing workarounds or with pointing you to feedback requests. Also, if you keep issues separate, you have a better chance of getting feedback from the people working on them - otherwise they will be split internally into new items that you cannot track.

 

Hope this helps.

 

Laurentiu

Answer 64

Based on the above suggestion, I have essentially copied the previously closed feedback as a new item.  In order to have MS look at and resolve this issue, please vote here:  https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328585

Thanks,
Joseph

Answer 65

I added some comments to this new request item, to clarify what I believe the core request should be.

 

The comments you made about implicit user/schema creation represent a separate set of issues and I am not clear what you request about them. I suggest opening a separate thread to discuss this behavior and the problems you see with it; then you can see if your concerns are addressed already or if you may need to open a separate feedback request. Even if you consider these two issues to be related and you disagree with my opinion, you can still create separate items and make them point to each other as related (just include the item links in your description/comments).

 

Hope this helps

Laurentiu

Answer 66

Here is the workaround I provided my users who want to use SSMS to create a table and were permissioned via a AD group:

Use t-sql to create a table with schema and name you want to use, e.g.:

Create Table dbo.ChuckTest (ProductID int)

 

Then modify to your heart’s content in SSMS (express or full product). The dbo. is a must, and could be other schema's. User still needs rights obviously to the database.

 

-Chuck Lathrope

http://www.sqlwebpedia.com

Answer 67

ComputerGuy_Chuck wrote:

Here is the workaround I provided my users who want to use SSMS to create a table and were permissioned via a AD group: Use t-sql to create a table with schema and name you want to use, e.g.: Create Table dbo.ChuckTest (ProductID int)   Then modify to your heart’s content in SSMS (express or full product). The dbo. is a must, and could be other schema's. User still needs rights obviously to the database.   -Chuck Lathrope http://www.sqlwebpedia.com

That's the problem though, people want to use the GUI to create ... at least in my experience, most developers don't know the syntax and are to lazy to look it up or learn it.

Answer 68

Ok when will this be addressed?  Please stop "fixing" things that are not broke, I cant even count the number of occurences where I have run into something that in 2000 was a simple task is now a complicated convuluted non functional task in 2005.

 

If I understand the arguments above, why would I even use a domain group ever if I can not define a default schema?

 

My scenario:

Domain group has all developers in it.. domain group assigned the authority. Developers must fully qualify any queries because their default schema = NULL ...(I am sure from above discussions I just havent hit the other issues associated with this yet)...... Assign the domain user individually (which defeats the whole purpose of groups) and then you can assign a default. My whole intention was to get away from having to manage individual user accounts.... Surely these 25 people are not the only ones running into this. Most people just dont feel like registering and providing input on something that has already been clearly defined above as an issue.

 Please provide a solution to using domain groups...

Thanks

Answer 69

Yes, very frustrating.  Be sure to also post here:  https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328585

 

Answer 70

 

Chuck

 

As a DBA, the comment you made that "most developers don't know the syntax" is the biggest single issue I have in supporting the databases.

 

The number of incredibly basic mistakes I have to fix because developers cannot be bothered to learn what their actions are doing under the covers takes up at least 10% of my time.

 

Personally, I think that if anyone does not know the syntax of what they are wanting to do then they should not be touching the system - I don't know any C# and I'n sure that no developer would like me hacking about with their front end code so why should a developer who can ONLY work with the SSMS GUI be allowed to touch my databases?

 

Schemas are new in SQL 2005 - learn about them and stop thinking that they are the same as 2000 was.  I would personally expect all developers touching my system to be explicitly using the schema.object reference in all their code .... just because they may have dbo or something else as their default schema does not mean that the eventual end users will as well and therefore there could be unpredictable actions from e.g. executing a stored procedure that refers to a table called 'foo' - if the developer had dbo as their default schema then the correct table is probably dbo.foo, but what if the end user had a default schema of BAR which also had a table called foo?   Instead of referencing dbo.foo they would instead get BAR.foo

 

I suggest that people start enforcing better coding practices and ALWAYS use schema.object in all references which then makes this entire issue redundant.

Answer 71

"I suggest that people start enforcing better coding practices and ALWAYS use schema.object in all references which then makes this entire issue redundant."

The default schema property exists for a reason. It is not redundant.

Yet the default schema property is broken because it does not work for users that map to a windows group logon.

And it is completely ridiculous that this has yet to be fixed.

 

One solution of course would be to make it so that if you had no default schema then you MUST code a schema when naming objects, but that’s not what Microsoft did. Instead, they made it SQL function irrationally. If you create an object and don’t specify dbo or another schema, it will create a schema, on the fly, named after you and put the object in that schema. This even when you have explicitly been DENIED create schema permission. There really can’t be any justification for this behavior. Either default schemas need to be made to work for windows group users, or users who have no default schema must be required to specify schema names. Period.

Answer 72

 

You can purchase Sql 2000 pretty cheap on ebay right now.  Problem solved.

Answer 73

BobJ0101 wrote:

Instead, they made it SQL function irrationally. If you create an object and don’t specify dbo or another schema, it will create a schema, on the fly, named after you and put the object in that schema. This even when you have explicitly been DENIED create schema permission. There really can’t be any justification for this behavior.

 

The justification is backward compatibility. In SQL Server 2000, when schemas didn't exist as a standalone entity, the concepts of schema and user were mixed, resulting in the ability to refer to an object T owned by alice as alice.T, even though there was no actual schema object called alice. In SQL Server 2005, if you say alice.T, then alice must be a real schema. It is because of this that in SQL Server 2005, if you create an object, a schema will be automatically created for you - this enables legacy applications that referred to alice.t in the absence of an alice schema to continue to work as they did in SQL Server 2000.

 

BobJ0101 wrote:

Either default schemas need to be made to work for windows group users, or users who have no default schema must be required to specify schema names. Period.

 

Sadly, neither works. First option is problematic because default schemas are not permissions - you cannot add them. If I am a member of groups A and B and each has a default schema, which one should be my default schema? Second option won't work because of backward compatibility - old applications won't know about default schemas, but still have to work in the 2005 word of schemas.

 

BobJ0101 wrote:

And it is completely ridiculous that this has yet to be fixed.

 

No, it is actually not surprising if you understand how such work is prioritized according to the number of customer requests. According to the item opened at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328585, there are only 28 ratings for this request as of August 22, 2008 (the item was recreated in February so this represents all the requests made in 6 months). 28 requests isn't enough to justify a high priority fix in this area and this is compounded by the fact that there is no simple fix for this issue. The suggestions made on this forum were usually quick fixes that addressed individual use scenarios and the other suggestions that were proposed internally are non trivial to implement and don't qualify as "fixes".

 

The bottom line is that this issue is known, but with the small number of requests received for addressing it, it does not gain the visibility to become a must fix issue - it will get passed over in favor of working on requests coming from larger groups of customers. And don't get me wrong: I want this issue to be fixed and I cast my vote for it - I even threw in 3 validations. But I'm still just one of those 28 persons that voted for this item. 28 requests in half a year is not an impressive number.

 

So my advice is to rate this issue as important or higher using the feedback item link included above. Getting the number of votes higher is the only sure way to have this issue addressed faster.

 

Hope this helps.

Answer 74

Laurentiu Cristofor wrote:

"I'm amazed at how long this thread has gone and the issue with the GUI has yet to be resolved."   Hi Laurentiu The issue in this thread is not a GUI issue. The GUI will not allow you to set a default schema for a group because the database doesn't allow that. You can vote on this issue at https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&wa=wsignin1.0&siteid=68 to bump the relevance of this item.

I want to try and clarify what I think the issue is that at least some people (including myself) are having with the GUI.

The problem is not that the GUI will not allow you to specify a default schema for a security group. This is true, and also understandable, given that the database engine will not allow it.

The problem is, that if you've been granted access to a database, by granting access to a group you are a member of, then the database tools return an error when trying to create a new table, and various other tasks. That is, right clicking on a database, and using the option to create a table fails, with a seemingly unrelated error message.

The error message is as follows:

Property DefaultSchema is not available for Database '[ART]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (SQLEditors)

In searching for help on this error message, this is where I ended up, along with the connect pages too, and I presume its where quite a few other people have ended up.

It is not an uncommon way of assigning roles to a server, and it is certainly a bug if the tools provided by Microsoft to administer Sql Server to fail completely on such a basic task.

If you believe it is impossible (for non-deterministic reasons) for a GUI to create a table for a user, who has been given access to a database because a group he is a member of has been given access; at least make sure this appears in the error message, grey out the option to create tables, etc. Personally however, I hope you can find a way to solve this problem satisfactorily, perhaps in the same way it is handled if the person is a member of the sysadmin role.. It works fine if that is the case

Finally, if you think that this discussion is in the wrong forum, or won't be seen by the SQL Server Tools developers (and you believe they are the right people to look into this), perhaps you could send the relevent people an email to take a look at this thread, or raise the issue yourself. Remember, we have paid a lot of money for this product and we are not Microsoft employees, and we're not Beta testers. I have made a pretty good effort at reporting the problems to Microsoft, and others have done much better than I have

Regards

Martin

Answer 75

  Unfortunately we are mixing the two issues again.

 

* SQL Server Management Studio (SSMS):  

 

  The failure to create a table (or other objects) using the GUI based on the group membership and permissions granted to the group is a GUI (SQL Server Management Studio), and I would really like to make this distinction so you can report the GUI problem using SQL Server Connect, and vote for it.

 

   While I absolutely agree that this is a bug that needs to be fixed on the user interface (and we have tried to push for it, unfortunately without success). Microsoft product teams listen to mayor pain points reported by customers and act accordingly; providing feedback to the appropriate team makes a lot of difference as this feedback will help the team in charge of SSMS to triage this fix appropriately.

 

* SQL Server Engine:

 

  The SQL Server Engine issue is to allow to specify a default schema for the Windows groups. The problem with this issue is, in case of a conflict, how to choose in a deterministic way the default schema. If you are still interested on this change, I also suggest using  SQL Server Connect to provide us with the appropriate feedback.

 

  We have been thinking about potential solutions for the engine issue, but before justifying the task (after all, we have finite resources and time constraints, so we really have to weigh the importance of this issue), we definitely would like to hear back from you while avoiding mixing this problem with the SSMS one.

 

   One potential solution for  this issue would require a deterministic rule (most likely hardcoded) to resolve the conflicts (for example, based on the group SID assign priorities), but it still a compromise as Windows group membership is completely outside the control of SQL Server, and we will have a risk that a change in group memberships in Windows may affect the SQL schema resolution as a side effect.

 

  I hope I was able to clarify the difference between the two issues, and I would like to encourage anyone who opens an item under SQL Server Connect for either issue to share the link in this thread,  and at the same time encourage the rest of the community to vote for the item that you think needs to be addressed.

 

   Thanks a lot,

- Raul Garcia

   SDE/T

   SQL Server Engine

 

Answer 76

Having just recently run into this issue, I have a thought. I am at a place where some developers prefer using the GUI in development for table creation, this is new, I am used to them creating them as scripts.

A couple thoughts on this:

1.) I use windows groups and Database Roles together to try and control permissions. I don't want developers in the DBO role or SA server role because that can cause false positives in unit testing. So I create DB roles for developers with access to the minimum securables necessary to do their job.

2.) We primarily use the DBO schema here for the time being. This could change but it might not. Being new I am starting to lay down the "law" here as a DBA. One of those items is SCHEMA QUALIFICATION... When it's followed and objects are created through T-SQL it works great.

3.) When objects are created through the designer, it fails because they can't select a schema. So this is actually good and bad:

 - Good: Maybe they should create the table in their own schema, work with it a bit, modify it, etc. Then when ready to create it in the common schema they script it out and modify the CREATE TABLE script to add DBO to the beginning and create it in the DBO schema.

- Bad: That is still a hassle and can cause issues.

So maybe the issue is not in the fact that you can't assign a schema to a group (although I could say maybe it should default to DBO, or maybe you pick a default and if no schema is assigned outside of the default it defaults to DBO).. Maybe the only real issue here (besdies not schema qualifying in scripts/procs/etc. which is a best practice that all of us DBAs should be enforcing...) but maybe the realy issue is that when creating an object through the GUI, you can't select a schema for the object.

If a user could pick a schema to create in from a drop down that would eliminate 25% of the headache. If we could only get developers to schema qualify that would get rid of 70% and if I could always remember to schema qualify myself that would give the final 5%...

Answer 77

I just read through this thread that spanned several years and was amazed that nobody actually figured out what the TRUE problem was until Mike Walsh nailed it. 

I will agree with all the moderators comments that it is not a good idea to assign a default schema to an AD group.  I would add that this is not really a problem since that can be solved by fully qualifying your T-SQL. 

The TRUE problem is that if you are trying to create an object using SSMS or even view the properties of an object that already exists, SSMS calls the DefaultSchema property and since there is no default schema for a user, you get an error. 

Let's look at these two thing independently and propose what should be easy fixes that would satisfy all my developers needs to use the SSMS GUI while still utilizing AD groups to assign permissions.

Can't view the properties of an object that already exists using SSMS if there is no default schema associated with the user. Why is the SSMS GUI checking for the user's default schema when viewing the properties of an object that already exists?  The object is already in a schema, just use that schema.  This should be an easy fix.  Enough said.

Can't create a new object using SSMS if there is no default schema associated with the user.  As Mike Walsh stated, "a user could pick a schema to create in from a drop down".  This also seems like a slam dunk and would satisfy all my developers needs for creating objects using SSMS GUI.

Please note that in my statement of the two issues, I never mention that an AD group could not be assigned a default schema.  I don't really care why the user doesn't have a default schema.  The true issue is that the SSMS GUI can not handle a user with no default schema. 

If my developers can specify what schema they are dealing with in T-SQL by fully qualifying object names, then they should have similar capabilities in the SSMS GUI!!!!!!