Default Schema in SQL 2005/SQL 2008

When you create a new AD group login in SQL Server and then create the Database user based of that login it is not possible to assign it a Default Schema. If you try to assign it a default login you get the following error:

TITLE: Microsoft SQL Server Management Studio
——————————

Create failed for User ‘Test’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15259)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1442&EvtSrc=MSSQLServer&EvtID=15259&LinkId=20476

——————————
BUTTONS:

OK
——————————

There is a Microsoft Connect article over it: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328585

What I had asked the people to do is qualify their object names with schema where it needs to be; even for dbo schema.

That’s the only workable work-around I had for them; other was create a login for each individual developer. Decided not to go that route because of number of users and turn around on the developer staff.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.