SQL Server Best Practices Article

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
Server Provisioning
User Provisioning
Summary
Appendix: Provisioning Script

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.

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.

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.

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.

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

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

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.

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:

• Securing SQL Server

• Permissions Hierarchy in SQL Server

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];

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

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.

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 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.

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

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

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 )

set @sql = 'GRANT CONNECT SQL TO ' + 
QUOTENAME(@login_name)
execute( @sql )

set @sql = 'DENY VIEW ANY DATABASE 
TO ' + QUOTENAME(@login_name)
execute( @sql )

Table 2   Database Creation for a Hosted Database User

set @sql = 'CREATE DATABASE ' + 
QUOTENAME(@db_name)
execute( @sql )

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 )

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 )

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 FULLTEXT CATALOG [' + 
@db_name + '_fulltext]  AS DEFAULT'
execute( @sql )

Table 3   Hosted Database User Creation and Provisioning

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

set @sql = 'ALTER LOGIN ' + 
QUOTENAME(@login_name)  + ' 
WITH DEFAULT_DATABASE = ' + 
QUOTENAME(@db_name)
execute( @sql )

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 )

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
'GRANT CONTROL ON SCHEMA::dbo TO ' + 
QUOTENAME(@login_name) 
+ ' WITH GRANT OPTION'
execute( @sql ) 

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

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
'GRANT ALTER ANY APPLICATION ROLE TO ' + 
QUOTENAME(@login_name)
execute( @sql )

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

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
'GRANT VIEW DEFINITION TO ' + 
QUOTENAME(@login_name)
execute( @sql )

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
'GRANT CREATE SCHEMA TO ' + 
QUOTENAME(@login_name)
execute( @sql )

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
+ 'GRANT VIEW DATABASE STATE TO 
' + QUOTENAME(@login_name) 
execute( @sql )

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
+ 'DENY ALTER ANY ASSEMBLY TO ' 
+ QUOTENAME(@login_name) 
execute( @sql )

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
+ 'DENY ALTER ANY ASYMMETRIC 
KEY TO ' + 
QUOTENAME(@login_name)
execute( @sql )

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
+ 'DENY ALTER ANY CERTIFICATE 
TO ' + QUOTENAME(@login_name) 
execute( @sql )

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

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

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

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
+ 'DENY ALTER ANY FULLTEXT CATALOG 
TO ' + QUOTENAME(@login_name)
execute( @sql )

set @sql = 'USE ' + 
QUOTENAME(@db_name) + char(10) 
+ 'DENY CHECKPOINT TO ' + QUOTENAME(@login_name) 
execute( @sql )

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

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

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