CREATE USER WITH DEFAULT_SCHEMA Issue
tdowning
Posts: 3
In SQL 2005 Users can not be created using the phrase WITH DEFAULT_SCHEMA=[dbo] if the user is Windows Group.
My company uses Windows Groups rather than individual logins to facilitate management.
Is there a way to work around this other than manually adding the group?
Why is it is assumed by Red-Gate that we want to create users WITH DEFAULT_SCHEMA
The script tool in Management Studio does not use this.
it simply uses CREATE USER [UserName] FOR LOGIN [Domain\Group]
My company uses Windows Groups rather than individual logins to facilitate management.
Is there a way to work around this other than manually adding the group?
Why is it is assumed by Red-Gate that we want to create users WITH DEFAULT_SCHEMA
The script tool in Management Studio does not use this.
it simply uses CREATE USER [UserName] FOR LOGIN [Domain\Group]
Comments
Until the bug is fixed the following work arounds are effective.
Work around number 1: Remove Windows Group assignments from the database users list. Not very practical but it does work.
Work around number 2: When comparing from scripts uncheck any windows group users from the list of items to compare. Then add windows group member after scripts are compete.
Work around 3: When creating the new blank database from scripts add any windows group users at that time before running the comparison and synchronization. This one seems to be the most effective that way if any permissions are established on database objects for that particular user the comparison and synchronization process will add them in.
Work around number 4: Create the new database using a Live Database instead of scripts. Not always a viable option if using source control and rolling back or creating a database from a different branch or revision.