When comparing a database to a script folder, SQL Compare appends the 'WITH DEFAULT_SCHEMA = [xxx]' option to CREATE USER statements where the user is mapped to a login that was created from a Windows Group. Such Users are therefore being incorrectly flagged as differing between source and target.
The statement containing the invalid syntax can be seen in the application's comparison window, an example of such a statement is as follows:
CREATE USER [MYDOMAIN\MyWindowsGroup]
FOR LOGIN [MYDOMAIN\MyWindowsGroup]
WITH DEFAULT_SCHEMA=[dbo]
GO
When the above is executed the following error is returned:
Msg 15259, Level 16, State 1, Line 1
The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.
SQL Compare: 8.1.0.360 Professional Edition
Microsoft SQL Server 2005 - 9.00.4207.00 (Intel X86) Dec 17 2008 14:34:38 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Chris
You have come across a known issue but there is a workaround. Please see the following knowledgebase article:
The statement containing the invalid syntax can be seen in the application's comparison window, an example of such a statement is as follows:
CREATE USER [MYDOMAIN\MyWindowsGroup]
FOR LOGIN [MYDOMAIN\MyWindowsGroup]
WITH DEFAULT_SCHEMA=[dbo]
GO
When the above is executed the following error is returned:
Msg 15259, Level 16, State 1, Line 1
The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.
SQL Compare: 8.1.0.360 Professional Edition
Microsoft SQL Server 2005 - 9.00.4207.00 (Intel X86) Dec 17 2008 14:34:38 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Chris