SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Default schema

Sadly, I haven't had much experience yet with SQL Server 2005 except for Reporting Services and SSIS. The majority of my databases are for third-party
applications that are not yet certified for SQL Server 2005 or require an upgrade so I'll be running 2000 for quite awhile at work.

This past weekend I was working on an article about scripting DBA tasks.  I wanted to make sure that I had sample code for both the 2000 and 2005 versions.  The code adds or removes AD accounts from SQL Server based on a list of accounts in a table.  I didn't realize that a default schema had to
be specified for the accounts in 2005 when mapped to a database user. If a default schema is not specified, a schema is created for the user.  Then, if  you want to remove the account, the schema must be removed first.

My original code to map an account called MyDomain\MyAccount looked like this:

 CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]

To make sure that a schema is not created for the user, specify the default
schema like this (substitue the appropriate schema for dbo):

 CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]
 WITH default_schema = [dbo]



Advice from Aunt Kathi

Kathi Kellenberger is a Sr. Consultant with Pragmatic Works. She is an author, speaker and trainer.


No comments.

Leave a Comment

Please register or log in to leave a comment.