Printed 2017/02/21 12:22AM

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]



Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.