When I try to set the default schema for my Windows Role/Group for Windows Server 2008 R2, it won't allow me to do so...I run:

ALTER USER [UCPSAR\Service Provision] WITH DEFAULT_SCHEMA=[ServiceProvision_Schema]

And I get the error message:

Msg 15259, Level 16, State 2, Line 1
The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

If I do a select to find out why

SELECT name, type_desc, default_schema_name
FROM sys.database_principals
WHERE type in ('S', 'U', 'G');

I end up with default_schema_name for my WINDOWS_GROUP being NULL

Is there any way to remedy this?

  • 1
    Are you concerned about this because schemas are being inadvertently created by the members of this group? Or are objects not being located? If you provide those details, there may be a workaround you can use. – Max Vernon Apr 19 '16 at 17:28
  • I want to narrow down the fields they have access to reading the best practices document seemed to indicate that the Default Schema should be set for this to work correctly. – leeand00 Apr 19 '16 at 17:33
  • I'm not sure I follow what you're saying. Do you have a link to those best practices? – Max Vernon Apr 19 '16 at 17:45
  • Here ya go: technet.microsoft.com/en-us/library/… – leeand00 Apr 19 '16 at 17:47
  • I read that document, but I still don't understand directly how that applies to you. That document is overly broad - perhaps you could edit your question to show details about your specific situation and about exactly what you are attempting to do. – Max Vernon Apr 19 '16 at 18:26

Upgrade to a modern version of SQL Server. As the error states, you cannot specify a default schema for a group. You can in SQL Server 2012 and newer.

| improve this answer | |
  • Everything Microsoft seems to work like this...do they just release things in beta to save money or what? I remember reading that SQL Server 2005 didn't even support the separation of Schemas and Schema Owners...none of this sounds even remotely like a completely developed product and yet somebody had to pay for it!!! – leeand00 Apr 19 '16 at 15:09
  • So you can't secure it using Windows Groups unless you upgrade? Well I mean I guess you can, you just can't set a default schema... – leeand00 Apr 19 '16 at 15:14
  • 1
    Every software product in use today adds features with new releases. – Robert L Davis Apr 19 '16 at 15:27
  • Yeah okay...that's true... – leeand00 Apr 19 '16 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.