SQL Server Best Practices Article

Article
01/28/2010

Published: April 30, 2007 | Updated : July 31, 2007

Writer: Alex DeNeui

Technical Reviewers: Bala Neerumalla, Rusland Ovechkin, Faith Allington, Mark Lium

Applies To: SQL Server 2005 SP2

Summary: SQL Server 2005 is an ideal database platform for use in shared and dedicated Web hosting environments. This paper provides best practices for configuring SQL Server 2005 to optimize security, tenant isolation, and the performance of your hosted SQL Server 2005 deployment. Sample scripts for provisioning users and databases for use in shared hosting are included.

Introduction

This document provides best practices and considerations for hosting services providers who are interested in deploying Microsoft® SQL Server™ 2005 in multi-tenant environments. Although designed primarily for Web hosting, it is suitable for other forms of hosting as well.

This guidance should not be used in lieu of careful planning and provisioning of SQL Server 2005.

Server Provisioning

The following sections highlight several areas where SQL Server 2005 features are useful to the needs of the hosting environment. These features can be tuned to improve the performance and security of SQL Server 2005.

Configuring Windows for Large Amounts of Memory

This section is useful for deployments with the following configuration:

The target deployment computer is a 32-bit server.
The target deployment computer has more than 2 GB of memory.

Several versions of Microsoft Windows®, including Windows Server™ 2003 and Windows XP Professional, can be configured to expand the address space of virtual memory supplied to aware applications when memory is available. This is useful for SQL Server 2005 because the size of this address space determines the size of the query plan cache.

The /3GB switch is specified in the Windows Boot.ini file. For information on setting this property, see Memory Support and Windows Operating Systems on Microsoft.com.

Provisioning Multiple Instances of SQL Server

In some scenarios, running multiple instances of SQL Server on a single server can support more users than a single instance of SQL Server. Using multiple instances could be useful in the following situations:

There are thousands of users and databases allocated to the database server.
The database server is a 32-bit version of SQL Server 2005.
The database server has more than 3 GB of RAM.
The database server has multiple CPUs.
There is contention for SQL Server memory used for caching compiled query plans.
- Memory contention can be debugged during normal server usage by looking at the sys.dm_exec_cached_plans dynamic management view (DMV) to check for plan reuse. (For more information, see sys.dm_exec_cached_plans in SQL Server Books Online.)
- The sys.dm_os_memory_clerks DMV can be used to see the entire size of the query plan cache. (For more information, see sys.dm_os_memory_clerks in SQL Server Books Online.)

To set up a named instance of SQL Server, supply the instance name when you run Setup. For more information, see Instance Name in SQL Server Books Online.

For more information on using multiple instances per server to solve multi-tenancy performance problems, see Microsoft SQL Server 2000 Scalability Project—Server Consolidation. This article has additional information that can be used to tune multi-instance deployments.

Using the AWE API for Servers with Large Amounts of Memory

For computers with more than 2 GB of memory (or 3 GB if /3GB is set for Windows), SQL Server 2005 can use the Address Windowing Extensions (AWE) API to access the extended memory space in order to store data pages. For more information on this technology, along with additional caveats, see Enabling AWE Memory for SQL Server in SQL Server Books Online.

To enable AWE in SQL Server 2005, run the following script:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'awe enabled', 1
RECONFIGURE
GO

CLR Integration with SQL Server

In SQL Server 2005, developers can use .NET Framework languages to write user-defined types, stored procedures, triggers, user-defined functions, and other SQL Server objects. CLR integration with SQL Server gives developers who are familiar with .NET programming the ability to write database objects by using the language they already understand. For more information on CLR integration, see Database Engine .NET Framework Programming in SQL Server Books Online. For security ramifications of CLR integration, see CLR Integration Security.

Along with the security mitigations (listed in the provisioning script in User Provisioning), CLR integration for SQL Server is a powerful feature for hosting users.

CLR integration is disabled by default in SQL Server 2005. To enable CLR integration, run the following configuration script under an account with sysadmin privileges:

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
GO

EXEC sp_configure ‘clr enabled’, 1
RECONFIGURE
GO

Query Cost Governor Limits

Using the query cost governor can prevent runaway queries from consuming lots of resources on a shared server.

Whenever SQL Server receives a query, the query optimizer generates an estimated time that the query is expected to take. The query cost governor limit can be used to cancel all queries that are expected to take more than a specified amount of time. However, it is possible for the estimated time to be incorrect, which will cause fast-running queries to be cancelled. The decision to implement query cost governor limits is a compromise between maintaining acceptable server performance under load versus the potential for generating customer support issues.

If you want to use query cost governor limits, the following command prevents all queries that have an estimated execution time of longer than five minutes from running:

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
GO

EXEC sp_configure ‘query governor cost limit’, 300
RECONFIGURE
GO

For more information about this limit, see query governor cost limit Option in SQL Server Books Online.

Security Provisioning

The following sections contain tips and tricks that can help you secure your SQL Server 2005 deployment.

Before beginning this section, read the following documentation:

Disabling or Renaming the Built-in sa Account

In SQL Server 2005, the built-in system administrator (sa) account was maintained for backward compatibility. Unless necessary for other purposes, this account should be disabled or renamed.

The following commands can be used to disable or rename this account:

ALTER LOGIN sa DISABLE;
ALTER LOGIN sa WITH NAME = [sys-admin];

Server-Level Dynamic Management Views

Dynamic management views (DMVs) are a new feature in SQL Server 2005 that give detailed information on the execution environment of the database engine. This information is useful for debugging abnormal operations on the server. It is unlikely that hosting users will require access to data contained within the DMVs. Furthermore, there is the potential of information disclosure if users have access to it.

To revoke access to server-level DMVs, run the following command under an account with sysadmin privileges:

DENY VIEW SERVER STATE TO public

Thwarting Luring Attacks

SQL Server 2005 contains several mechanisms for executing user-defined code: DDL triggers, DML triggers, stored procedures, user-defined functions, and others. These mechanisms can potentially be used by malicious users. Users who induce administrators to run code can potentially elevate their own privilege. In the case of DDL and DML triggers, the administrator does not even have to directly execute code for this to happen. All that is necessary is for the administrator to perform standard SELECT, CREATE, UPDATE, DROP, or ALTER statements. For more information see Managing Trigger Security in SQL Server Books Online.

Administrators must be cognizant of this when they perform operations in a user's database. Administrators should not run untrusted user code, and should also disable any triggers that might fire. In particular, DDL triggers have a high potential for abuse. The provisioning guidance in User and Database Provisioning explains how to restrict users from creating DDL triggers.

Disabling Database DDL Triggers

In situations where an administrator might want to run DDL statements in an untrusted user database, the following script should be employed. This script disables all active database DDL triggers, contains a place where maintenance logic can be added, and then re-enables the database DDL triggers.

DECLARE @trigger_name sysname
DECLARE @cmd NVARCHAR(1024)

SELECT [name] INTO #enabled_triggers FROM sys.triggers WHERE 
is_disabled = 1 AND parent_class = 0 AND is_ms_shipped = 0

DECLARE triggers CURSOR FOR SELECT [name] FROM #enabled_triggers
OPEN triggers

FETCH NEXT FROM triggers INTO @trigger_name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'DISABLE TRIGGER ' + 
    QUOTENAME(@trigger_name) + ' ON DATABASE'
    EXECUTE(@cmd)
    FETCH NEXT FROM triggers INTO @trigger_name
END;
CLOSE triggers;
DEALLOCATE triggers;

--
-- DDL operations occur here
--

DECLARE triggers CURSOR FOR SELECT [name] FROM #enabled_triggers
OPEN triggers

FETCH NEXT FROM triggers INTO @trigger_name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'ENABLE TRIGGER ' + QUOTENAME(@trigger_name) 
            + ' ON DATABASE'
    EXECUTE(@cmd)
    FETCH NEXT FROM triggers INTO @trigger_name
END;
CLOSE triggers;
DEALLOCATE triggers;
DROP TABLE #enabled_triggers

Disabling Database DML Triggers

The following script disables all DML triggers in a user database. The script is designed to be used when an administrator runs DML operations (SELECT, DELETE, INSERT, UPDATE).

DECLARE @schema_name sysname, @trigger_name sysname, 
    @object_name sysname;
DECLARE @sql nvarchar(max) ;
DECLARE trig_cur CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT SCHEMA_NAME(schema_id) AS schema_name,
        name AS trigger_name,
        OBJECT_NAME(parent_object_id) as object_name
    FROM sys.objects WHERE type in ('TR', 'TA') ;

OPEN trig_cur ;
FETCH NEXT FROM trig_cur INTO @schema_name, 
    @trigger_name, @object_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = 'DISABLE TRIGGER ' + QUOTENAME(@schema_name) + '.'
        + QUOTENAME(@trigger_name) +
        ' ON ' + QUOTENAME(@schema_name) + '.' 
        + QUOTENAME(@object_name) + ' ; ' ;
    EXEC (@sql) ;
    FETCH NEXT FROM trig_cur INTO @schema_name, 
@trigger_name, @object_name;
END
GO

CLOSE trig_cur ;
DEALLOCATE trig_cur;

The script in the previous sections can be modified to suit other scenarios as well.

Deploying SQL Server Directly on the Internet

When deploying SQL Server 2005 for Web hosting workloads, the majority of servers are situated behind firewalls that restrict access to the server ports from the Internet. This reduces the attack surface of the database, thereby increasing security. Unfortunately, placing SQL Server behind the firewall also prevents SQL Server management tools (including SQL Server Management Studio, sqlcmd, and other tools) from communicating with SQL Server. In these deployments, SQL Server cannot be directly connected to. There are a number of third-party Web-based administration tools that can be deployed for end-user use. Microsoft has built a Web-based administration console, available at SQL Server Web Tools on CodePlex.

If you choose to deploy SQL Server 2005 directly to the Internet, keep the following in mind:

Deploying SQL Server 2005 directly on the Internet could increase the potential of denial-of-service or other malicious attacks against the database.
Ensuring that Windows Firewall is configured to block all unnecessary ports from accessing the server helps to protect your server
Running the SQL Server Surface Area Configuration tool and disabling all unnecessary features and services helps reduce your attack surface area.

SQL Server Surface Area Configuration

SQL Server 2005 includes the Surface Area Configuration tool, which is designed so that system administrators can easily enable or disable portions of the SQL Server surface area. This tool is on the Start menu (Start , Programs , Microsoft SQL Server 2005 , Configuration Tools , SQL Server Surface Area Configuration).

When you start the Surface Area Configuration tool, the following screen appears:

SQL Server 2005 Surface Area Configuration tool

To configure the surface area for SQL Server, use the two links near the bottom of the tool window.

For detailed instructions for using the tool to configure services and connections, see Surface Area Configuration for Services and Connections. For instructions for configuring SQL Server features, see Surface Area Configuration for Features.

All of the defaults in this tool are recommended, except for the CLR integration property as mentioned in CLR Integration with SQL Server.

User Provisioning

This section describes best practices for provisioning SQL Server logins, users, and databases for use in a shared Web hosting environment.

User and Database Provisioning

The Appendix includes a script that illustrates best practices for configuring a login and database for SQL Server 2005 suitable in a shared hosting environment.

The following tables describe the components of this script, line by line, along with the rationale for the guidance and links to additional information.

Table 1 Login Creation for a Hosted Database User

Command	Explanation
set @sql = 'CREATE LOGIN ' + QUOTENAME(@login_name) + ' WITH PASSWORD = ''' + REPLACE(@password, '''', '''''') + '''' execute( @sql )	Creates a login with the specified user name and password.
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name) + ' WITH CHECK_POLICY = ON' execute( @sql )	Enables password strength policy on the login.
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name) + ' WITH CHECK_EXPIRATION = OFF' execute( @sql )	Removes password aging policies on the login.
set @sql = 'GRANT CONNECT SQL TO ' + QUOTENAME(@login_name) execute( @sql )	Grants the user permissions to connect to SQL Server.
set @sql = 'DENY VIEW ANY DATABASE TO ' + QUOTENAME(@login_name) execute( @sql )	Prevents the user from seeing databases not owned by the user when issuing a command such as: *SELECT FROM sys.databases This setting also prevents this database from being displayed in the Object Explorer window in SQL Server Management Studio for users who are not the db_owner of their own database. If there are users who use SQL Server Management Studio and need to access databases where the account is not db_owner, this setting is not recommended.** For more information, see VIEW ANY DATABASE Permission.

Table 2 Database Creation for a Hosted Database User

Command	Explanation
set @sql = 'CREATE DATABASE ' + QUOTENAME(@db_name) execute( @sql )	Creates a database with the given name.
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' MODIFY FILE (NAME=''' + QUOTENAME(@db_name) + ''', MAXSIZE=200, SIZE=5, FILEGROWTH=5)' -- Set max data file size execute( @sql )	Limits the size of the database and specifies file growth characteristics.
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' MODIFY FILE (NAME=''' + QUOTENAME(@db_name) + '_log'', MAXSIZE=75, SIZE=2, FILEGROWTH=5)' -- set max log file size execute( @sql )	Limits the size of the transaction log and specifies file growth characteristics.
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' SET TRUSTWORTHY OFF' execute( @sql )	Sets the database as untrustworthy, meaning that objects in the database cannot access objects in other databases in an impersonated context.
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' SET AUTO_CLOSE ON' execute( @sql )	Enables auto-close on the database. This setting closes the database asynchronously when it is not in use, freeing system resources.
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' SET AUTO_SHRINK ON' execute( @sql )	Enables automatic shrinking of database files during periodic checks.
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' SET AUTO_UPDATE_STATISTICS ON' execute( @sql )	Enables automatic updating of statistics for the database.
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' SET AUTO_UPDATE_STATISTICS_ASYNC ON' execute( @sql )	Enables asynchronous updates of statistics for the database.
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'CREATE FULLTEXT CATALOG [' + @db_name + '_fulltext] AS DEFAULT' execute( @sql )	Creates a default full-text catalog for the database.

Table 3 Hosted Database User Creation and Provisioning

Command	Explanation	Additional Information
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'CREATE USER ' + QUOTENAME(@login_name) + ' FOR LOGIN ' + QUOTENAME(@login_name) execute( @sql )	Creates a user for the specified login in the database.
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name) + ' WITH DEFAULT_DATABASE = ' + QUOTENAME(@db_name) execute( @sql )	Changes the default database for the login to the newly created database.
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'exec sp_addrolemember ''db_ddladmin'', ''' + REPLACE(@login_name, '''', '''''') + '''' execute( @sql )	Adds the user to the db_ddladmin role. This role gives the ability to run DDL in the database.	db_ddladmin in SQL Server Books Online
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'exec sp_addrolemember ''db_datareader'', ''' + REPLACE(@login_name, '''', '''''') + '''' execute( @sql )	Adds the user to the db_datareader role. This role gives the user the ability to select from tables and views in the database.	db_datareader in SQL Server Books Online
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'exec sp_addrolemember ''db_datawriter'', ''' + REPLACE(@login_name, '''', '''''') + '''' execute( @sql )	Adds the user to the db_datawriter role. This role gives the user the ability to insert and update tables and views in the database.	db_datawriter
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) 'GRANT CONTROL ON SCHEMA::dbo TO ' + QUOTENAME(@login_name) + ' WITH GRANT OPTION' execute( @sql )	Grants the user the ability to control all objects within the dbo schema.
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'GRANT SHOWPLAN TO ' + QUOTENAME(@login_name) execute( @sql )	Grants the user the ability to view query plan information.	SET SHOWPLAN_ALL
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) 'GRANT ALTER ANY APPLICATION ROLE TO ' + QUOTENAME(@login_name) execute( @sql )	Grants the user the ability to create and alter application roles.	CREATE APPLICATION ROLE
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) 'GRANT ALTER ANY ROLE TO ' + QUOTENAME(@login_name) execute( @sql )	Grants the user the ability to create and alter roles and role membership.	CREATE ROLE
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) 'GRANT VIEW DEFINITION TO ' + QUOTENAME(@login_name) execute( @sql )	Grants the user the ability to view the definitions of objects within the database.	sp_helptext
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) 'GRANT CREATE SCHEMA TO ' + QUOTENAME(@login_name) execute( @sql )	Grants the user the ability to create schemas within the database.	CREATE SCHEMA
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'GRANT VIEW DATABASE STATE TO ' + QUOTENAME(@login_name) execute( @sql )	Grants the user the ability to view database-level DMV information.	Dynamic Management Views and Functions
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY ALTER ANY ASSEMBLY TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to create or alter assemblies in the database. CREATE ASSEMBLY can be used to open files in the file system by using the credentials of the SQL Server account. A workaround for creating assemblies is given in the stored procedure described in the script. See Stored Procedure for Creating Assemblies.	CREATE ASSEMBLY
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY ALTER ANY ASYMMETRIC KEY TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to create or alter asymmetric keys in the database. CREATE ASYMMETIRC KEY can be used to open files in the file system by using the credentials of the SQL Server account.	CREATE ASYMMETRIC KEY
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY ALTER ANY CERTIFICATE TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to create or alter certificates in the database. CREATE CERTIFICATE can be used to open files in the file system using the credentials of the SQL Server account.	CREATE CERTIFICATE
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY ALTER ANY DATASPACE TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to create or alter filegroups or database files in the database. CREATE PARTITION FUNCTION and CREATE PARTITION SCHEME can be used to change how data is stored in filegroups.	CREATE PARTITION FUNCTION
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY ALTER ANY DATABASE DDL TRIGGER TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to create or alter database DDL triggers in the database. Database DDL triggers can be used in a luring attack to elevate the privileges of the hosting user. See Thwarting Luring Attacks in this document.	Managing Trigger Security.
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY CREATE FULLTEXT CATALOG TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to create or alter full-text catalogs in the database. CREATE FULLTEXT CATALOG can be used to create files in the file system by using the credentials of the SQL Server account.	CREATE FULLTEXT CATALOG
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY ALTER ANY FULLTEXT CATALOG TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to alter full-text catalogs in the database. ALTER FULLTEXT CATALOG can be used to open files in the file system by using the credentials of the SQL Server account.	ALTER FULLTEXT CATALOG
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 'DENY CHECKPOINT TO ' + QUOTENAME(@login_name) execute( @sql )	Denies the user the ability to checkpoint a database.	CHECKPOINT

Stored Procedure for Creating Assemblies

The default configurations for a user in the User and Database Provisioning section do not give users the ability to create CLR assemblies; this is to prevent potential security issues with the file system.

However, since many developers want to use CLR functionality, this script includes a workaround: a stored procedure that can be used to create assemblies and is installable by the administrator. The script, which can be incorporated into an existing management interface, can be used by end users to create assemblies in a secure manner.

Note: This stored procedure is only necessary if CLR integration is enabled as described in CLR Integration with SQL Server.

CREATE PROCEDURE dbo.CreateAssembly
      @assemblyBits varbinary(max),
      @asmname sysname
AS
      DECLARE @sql NVARCHAR(max)

      BEGIN TRY
            DECLARE @username sysname
            SELECT @username = CURRENT_USER    
    
            set @sql = 'EXECUTE AS USER = ''dbo''' + CHAR(10) + 
                        'CREATE ASSEMBLY ' + QUOTENAME(@asmname) + 
                            ' FROM @assemblyBits WITH PERMISSION_SET = SAFE' + CHAR(10) + 
                        'ALTER AUTHORIZATION ON Assembly::' + QUOTENAME(@asmname) + 
                                    ' TO ' + QUOTENAME(@username) +                               CHAR(10) +
                        'REVERT'
            exec sp_executesql @sql, @parameters = 
                  N'@assemblyBits varbinary(max)', 
                  @assemblyBits = @assemblyBits 

            PRINT 'Assembly ''' + @asmname + ''' created';
      END TRY
      BEGIN CATCH
            PRINT 'Error occured: ' + ERROR_MESSAGE() 
      END CATCH         
GO

Summary

SQL Server 2005 is an ideal database platform for use in shared/multi-tenant Web hosting environments. The mix of database developer features (CLR integration, XML, full-text), the ASP.NET programming language, and the integrated Microsoft Visual Studio development environment creates an easy-to-use and powerful system for developing Web applications. The guidance in this paper gives best practices for configuring SQL Server 2005 to optimize security, tenant isolation, and the performance of your shared SQL Server 2005 deployment.

If you have questions, please email us: sqlhost@microsoft.com

Appendix: Provisioning Script

The following script creates a login for a given user name, defined by @login_name. The script then generates a database, the name of which is defined by @db_name, and provisions the given login to have access to the database.

declare @login_name sysname
declare @db_name sysname
declare @password sysname

SET @login_name = 'LOGINNAME'
SET @password ='PASSWORD'
SET @db_name = 'DATABASE_NAME'


declare @sql nvarchar(max)

exec sp_validname @login_name
exec sp_validname @db_name

-- Creating the login
--
set @sql = 'CREATE LOGIN ' + QUOTENAME(@login_name) + 
    ' WITH PASSWORD = ''' + REPLACE(@password, '''', '''''') + ''''
execute( @sql )
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name)  
    + ' WITH CHECK_POLICY = ON'
execute( @sql )
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name)  + 
    ' WITH CHECK_EXPIRATION = OFF'
execute( @sql )

-- Allows the user to connect to the server
set @sql = 'GRANT CONNECT SQL TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Removes the ability to select from sys.databases
set @sql = 'DENY VIEW ANY DATABASE TO ' + QUOTENAME(@login_name)
execute( @sql )



-- Creating the database
--
set @sql = 'CREATE DATABASE ' + QUOTENAME(@db_name)
execute( @sql )

set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + 
    ' MODIFY FILE (NAME=''' + @db_name + 
    ''', MAXSIZE=200, SIZE=5, FILEGROWTH=5)' -- Set max data file size
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + 
    ' MODIFY FILE (NAME=''' + @db_name + 
    '_log'', MAXSIZE=75, SIZE=2, FILEGROWTH=5)' -- set max log file size
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + 
    ' SET TRUSTWORTHY OFF'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + 
    ' SET AUTO_CLOSE ON'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + 
    ' SET AUTO_SHRINK ON'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + 
    ' SET AUTO_UPDATE_STATISTICS ON'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + 
    ' SET AUTO_UPDATE_STATISTICS_ASYNC ON'
execute( @sql )

set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'CREATE USER ' + QUOTENAME(@login_name) + 
    ' FOR LOGIN ' + QUOTENAME(@login_name)
execute( @sql )

-- Create a default full-text catalog
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'CREATE FULLTEXT CATALOG [' + @db_name + '_fulltext]  AS DEFAULT'
execute( @sql )

-- Set the user to use the database created
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name)  + 
    ' WITH DEFAULT_DATABASE = ' + QUOTENAME(@db_name)
execute( @sql )


-- Add the user and configure
--
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'exec sp_addrolemember ''db_ddladmin'', ''' + 
    REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'exec sp_addrolemember ''db_datareader'', ''' + 
    REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'exec sp_addrolemember ''db_datawriter'', ''' + 
    REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )

-- ******************************
-- Additional privledges granted
-- ******************************

-- Gives user ability control the DBO schema
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'GRANT CONTROL ON SCHEMA::dbo TO ' + QUOTENAME(@login_name) 
    + ' WITH GRANT OPTION' 
execute( @sql )

-- Needed to execute showplan
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'GRANT SHOWPLAN TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to manipulate application roles
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'GRANT ALTER ANY APPLICATION ROLE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to manipulate roles
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'GRANT ALTER ANY ROLE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to view definitions of objects
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'GRANT VIEW DEFINITION TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to create schemas
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'GRANT CREATE SCHEMA TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed for database-level DMVs
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'GRANT VIEW DATABASE STATE TO ' + QUOTENAME(@login_name) 
execute( @sql )

-- ******************************
-- Additional privledges denied
-- ******************************

-- Can be used to access the file system using SQL Server 
-- credentials.  (Both CREATE and ALTER.)
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY ALTER ANY ASSEMBLY TO ' + QUOTENAME(@login_name) 
execute( @sql )

-- Can be used to access the file system using the SQL Server
-- credentials.  (Both CREATE and ALTER)
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY ALTER ANY ASYMMETRIC KEY TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Can be used to access the file system using the SQL Server
-- credentials.  (Both CREATE and ALTER)
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY ALTER ANY CERTIFICATE TO ' + QUOTENAME(@login_name) 
execute( @sql )

-- Partition, filegroups
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY ALTER ANY DATASPACE TO ' + QUOTENAME(@login_name) 
execute( @sql )

-- Database DDL triggers
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY ALTER ANY DATABASE DDL TRIGGER TO ' + QUOTENAME(@login_name) 
execute( @sql )

 -- Creates files for catalog
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY CREATE FULLTEXT CATALOG TO ' + QUOTENAME(@login_name)
execute( @sql )

 -- Can be used to rebuild catalogs and move their locations
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY ALTER ANY FULLTEXT CATALOG TO ' + QUOTENAME(@login_name)
execute( @sql )

-- No need to checkpoint
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'DENY CHECKPOINT TO ' + QUOTENAME(@login_name) 
execute( @sql )
GO

-- *****************************
-- Stored procedure that can be safely used to 
-- create an assembly from a serialized varbinary
--
-- *****************************

-- Creating a stored procedure that is used to safely create assemblies
CREATE PROCEDURE dbo.CreateAssembly
      @assemblyBits varbinary(max),
      @asmname sysname
AS
      DECLARE @sql NVARCHAR(max)

      BEGIN TRY
                  DECLARE @username sysname
                  SELECT @username = CURRENT_USER
            set @sql = 'EXECUTE AS USER = ''dbo''' + CHAR(10) + 
                  'CREATE ASSEMBLY ' + QUOTENAME(@asmname) + 
                  ' FROM @assemblyBits WITH PERMISSION_SET = SAFE' + CHAR(10) + 
                  'ALTER AUTHORIZATION ON Assembly::' + QUOTENAME(@asmname) + 
                  ' TO ' + QUOTENAME(@username) + CHAR(10) +
                  'REVERT'
            exec sp_executesql @sql, @parameters = 
                  N'@assemblyBits varbinary(max)', 
                  @assemblyBits = @assemblyBits 
            PRINT 'Assembly ''' + @asmname + ''' created';
      END TRY
      BEGIN CATCH
            PRINT 'Error occured: ' + ERROR_MESSAGE() 
      END CATCH         
GO