2

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?

5
  • 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.
    – Hannah Vernon
    Apr 19, 2016 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, 2016 at 17:33
  • I'm not sure I follow what you're saying. Do you have a link to those best practices?
    – Hannah Vernon
    Apr 19, 2016 at 17:45
  • Here ya go: technet.microsoft.com/en-us/library/…
    – leeand00
    Apr 19, 2016 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.
    – Hannah Vernon
    Apr 19, 2016 at 18:26

1 Answer 1

2

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.

4
  • 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, 2016 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, 2016 at 15:14
  • 1
    Every software product in use today adds features with new releases. Apr 19, 2016 at 15:27
  • Yeah okay...that's true...
    – leeand00
    Apr 19, 2016 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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