CREATE USER x FOR LOGIN [windows group] WITH DEFAULT_SCHEMA=

Aline

Hello

I created with SQL Compare 8.1 folders with scripts.
As 2nd step I tried to synch from these scripts to a sandbox db.

But it failed due to this error:

The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

for

CREATE USER [x] FOR LOGIN [windows group] WITH DEFAULT_SCHEMA=[y]

When I checked in the script generated in the 1st step, it goes without the WITH-Definition.

Is there an option to prevent this?

Thanks for your help
Aline

Chris Auckland

Thanks for your post.

We happen to already have a case open for this, but it is quite a tricky issue.

The problem is that the syntax for a windows user and a windows group is exactly the same, so the script reader cannot know if something is a user or a group without contacting the NT domain - bad idea.

We are hoping to find a solution for the next major version of SQL Compare, but it looks like any solution will require assumptions to be made, which could result in inconsistent results.

The case open for this is SC-4057.

Another customer has made some helpful suggestions on another thread, that you may find useful:
http://www.red-gate.com/MessageBoard/vi ... php?t=7783

danere

I have a bit of a novel workaround for this if anyone is interested, although you will need to write some .NET code to parse the generated script file from SQL Compare.

In our shop, all of our Active Directory groups follow a convention where they begin with the string "SG_" after the domain, e.g. FLEXIRENT\SG_Database_ReadOnly_Users.

So we just use a regular expression to find all matches for the offending DEFAULT_SCHEMA string and remove them:

*snip*

string CreateUserStatementRegEx =  "(?<=CREATE\\sUSER\\s\\[.*?\\\\SG_.*?)WITH\\sDEFAULT_SCHEMA=\\[.*?\\]";
                GeneratedSQLBatchString = Regex.Replace(GeneratedSQLBatchString, CreateUserStatementRegEx, "", RegexOptions.IgnoreCase);

*snip*

If you don't use such a naming convention, another way you could do it is use the "RegEx.Matches(..)" method and query Active Directory from your code to determine the object type, and set the "Value" property on the returned MatchCollection items appropriately.

Hope this helps someone!

Dan