Category: SQL 2012

SQL Server Fixed Server Role Permissions

SQL Server Fixed Server Role Permissions – run – sp_srvrolepermission

ServerRole Permission

bulkadmin Add member to bulkadmin

bulkadmin BULK INSERT

dbcreator Add member to dbcreator

dbcreator ALTER DATABASE

dbcreator CREATE DATABASE

dbcreator DROP DATABASE

dbcreator Extend database

dbcreator RESTORE DATABASE

dbcreator RESTORE LOG

dbcreator sp_renamedb

diskadmin Add member to diskadmin

diskadmin DISK INIT

diskadmin sp_addumpdevice

diskadmin sp_diskdefault

diskadmin sp_dropdevice

processadmin Add member to processadmin

processadmin KILL

securityadmin Add member to securityadmin

securityadmin Grant/deny/revoke CREATE DATABASE

securityadmin Read the error log

securityadmin sp_addlinkedsrvlogin

securityadmin sp_addlogin

securityadmin sp_defaultdb

securityadmin sp_defaultlanguage

securityadmin sp_denylogin

securityadmin sp_droplinkedsrvlogin

securityadmin sp_droplogin

securityadmin sp_dropremotelogin

securityadmin sp_grantlogin

securityadmin sp_helplogins

securityadmin sp_password

securityadmin sp_remoteoption (update)

securityadmin sp_revokelogin

serveradmin Add member to serveradmin

serveradmin dbcc freeproccache

serveradmin RECONFIGURE

serveradmin SHUTDOWN

serveradmin sp_configure

serveradmin sp_fulltext_service

serveradmin sp_tableoption

setupadmin Add member to setupadmin

setupadmin Add/drop/configure linked servers

setupadmin Mark a stored procedure as startup

sysadmin Add extended procedures

sysadmin Add member to bulkadmin

sysadmin Add member to dbcreator

sysadmin Add member to diskadmin

sysadmin Add member to processadmin

sysadmin Add member to securityadmin

sysadmin Add member to serveradmin

sysadmin Add member to setupadmin

sysadmin Add member to sysadmin

sysadmin Add/drop to/from db_accessadmin

sysadmin Add/drop to/from db_backupoperator

sysadmin Add/drop to/from db_datareader

sysadmin Add/drop to/from db_datawriter

sysadmin Add/drop to/from db_ddladmin

sysadmin Add/drop to/from db_denydatareader

sysadmin Add/drop to/from db_denydatawriter

sysadmin Add/drop to/from db_owner

sysadmin Add/drop to/from db_securityadmin

sysadmin Add/drop/configure linked servers

sysadmin All DDL but GRANT, REVOKE, DENY

sysadmin ALTER DATABASE

sysadmin BACKUP DATABASE

sysadmin BACKUP LOG

sysadmin BULK INSERT

sysadmin CHECKPOINT

sysadmin Complete SETUSER SQL user

sysadmin Constraints on System tables

sysadmin CREATE DATABASE

sysadmin Create indices on system tables

sysadmin Create/delete/modify system tables

sysadmin dbcc change ‘on’ rules

sysadmin dbcc checkalloc

sysadmin dbcc checkdb

sysadmin dbcc checkfilegroup

sysadmin dbcc checkident

sysadmin dbcc checktable

sysadmin dbcc cleantable

sysadmin dbcc dbreindex

sysadmin dbcc dropcleanbuffers

sysadmin dbcc freeproccache

sysadmin dbcc inputbuffer

sysadmin dbcc outputbuffer

sysadmin dbcc pintable

sysadmin dbcc proccache

sysadmin dbcc setcpuweight

sysadmin dbcc setioweight

sysadmin dbcc show_statistics

sysadmin dbcc showcontig

sysadmin dbcc showoptweights

sysadmin DBCC ShrinkDatabase

sysadmin dbcc shrinkfile

sysadmin dbcc traceon/off

sysadmin dbcc updateusage

sysadmin DELETE permission on any object

sysadmin DENY

sysadmin DISK INIT

sysadmin DROP DATABASE

sysadmin EXECUTE any procedure

sysadmin Extend database

sysadmin GRANT

sysadmin Grant/deny/revoke CREATE DATABASE

sysadmin INSERT permission on any object

sysadmin KILL

sysadmin Mark a stored procedure as startup

sysadmin Raiserror With Log

sysadmin Read the error log

sysadmin RECONFIGURE

sysadmin REFERENCES permission on any table

sysadmin RESTORE DATABASE

sysadmin RESTORE LOG

sysadmin REVOKE

sysadmin SELECT permission on any object

sysadmin SHUTDOWN

sysadmin sp_addapprole

sysadmin sp_addlinkedsrvlogin

sysadmin sp_addlogin

sysadmin sp_addrole

sysadmin sp_addrolemember

sysadmin sp_addumpdevice

sysadmin sp_adduser

sysadmin sp_altermessage

sysadmin sp_approlepassword

sysadmin sp_change_users_login

sysadmin sp_changedbowner

sysadmin sp_changeobjectowner

sysadmin sp_configure

sysadmin sp_dbcmptlevel

sysadmin sp_defaultdb

sysadmin sp_defaultlanguage

sysadmin sp_denylogin

sysadmin sp_diskdefault

sysadmin sp_dropapprole

sysadmin sp_dropdevice

sysadmin sp_droplinkedsrvlogin

sysadmin sp_droplogin

sysadmin sp_dropremotelogin

sysadmin sp_droprole

sysadmin sp_droprolemember

sysadmin sp_dropuser

sysadmin sp_fulltext_catalog

sysadmin sp_fulltext_column

sysadmin sp_fulltext_database

sysadmin sp_fulltext_service

sysadmin sp_fulltext_table

sysadmin sp_grantdbaccess

sysadmin sp_grantlogin

sysadmin sp_helplogins

sysadmin sp_password

sysadmin sp_recompile

sysadmin sp_refreshview

sysadmin sp_remoteoption

sysadmin sp_remoteoption (update)

sysadmin sp_rename

sysadmin sp_renamedb

sysadmin sp_revokedbaccess

sysadmin sp_revokelogin

sysadmin sp_tableoption

sysadmin sp_updatestats

sysadmin TRUNCATE TABLE

sysadmin UPDATE permission on any object

sysadmin USE to a suspect database

 

Unable to start execution of step 1 (reason: JobOwner domain\domainuser

On a server running SQL 2014 SSIS with SSISDB configured, one of the SQL Agent jobs configured to run using a proxy account kept failing with the error below.

Message

Unable to start execution of step 1 (reason: JobOwner domain\domainuser

doesn‘t have permissions to use proxy 1 for subsystem SSIS). The step failed.

Solution that worked for me in my situation. 

Query your proxys and see if that proxy is disabled. If so, enable it.

SELECT * FROM msdb.dbo.sysproxies

USE msdb ;

GO

EXEC dbo.sp_update_proxy

@proxy_name = ‘MyProxyUSerUser’,

@enabled = 1;

GO

 

Sample Configurationfile.ini

Below is a sample configurationfile.ini which can be used for an unattended/automatic installation of SQL 2012/2014

This file is usually found here [for SQL 2012 installs]:

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

For 2014 Installations, change 110 to 120 in the above path. To generate this, run the GUI setup almost to the end, answering installation questions and then exit before kicking off the installation when the gui shows in a box that this file .ini has been created and will be used for the gui setup of SQL Server.

See this reference webpage for more details – click here.

Modify and save as a text file and use as needed, as shown below, running the setup.exe from your SQL Serverinstallation CDROM/installation program folder.

Setup.exe /ConfigurationFile=MyConfigurationFile.INI

=====================

;SQL Server 2012 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

ACTION=”Install”

; Detailed help for command line argument ENU has not been defined yet.

ENU=”True”

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.

UIMODE=”Normal”

; Setup will not display any user interface.

QUIET=”False”

; Setup will display progress only, without any user interaction.

QUIETSIMPLE=”False”

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.

UpdateEnabled=”True”

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.

FEATURES=SQLENGINE,FULLTEXT

; Specify the location where SQL Server Setup will obtain product updates. The valid values are “MU” to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.

UpdateSource=”MU”

; Displays the command line parameters usage

HELP=”False”

; Specifies that the detailed Setup log should be piped to the console.

INDICATEPROGRESS=”False”

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.

X86=”False”

; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.

INSTALLSHAREDDIR=”C:\Program Files\Microsoft SQL Server”

; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.

INSTALLSHAREDWOWDIR=”C:\Program Files (x86)\Microsoft SQL Server”

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).

INSTANCENAME=”SVR2″

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.

INSTANCEID=”SVR2″

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.

SQMREPORTING=”False”

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.

ERRORREPORTING=”False”

; Specify the installation directory.

INSTANCEDIR=”C:\Program Files\Microsoft SQL Server”

; Agent account name

AGTSVCACCOUNT=”NT Service\SQLAgent$SVR2″

; Auto-start service after installation.

AGTSVCSTARTUPTYPE=”Automatic”

; CM brick TCP communication port

COMMFABRICPORT=”0″

; How matrix will use private networks

COMMFABRICNETWORKLEVEL=”0″

; How inter brick communication will be protected

COMMFABRICENCRYPTION=”0″

; TCP port used by the CM brick

MATRIXCMBRICKCOMMPORT=”0″

; Startup type for the SQL Server service.

SQLSVCSTARTUPTYPE=”Automatic”

; Level to enable FILESTREAM feature at (0, 1, 2 or 3).

FILESTREAMLEVEL=”0″

; Set to “1” to enable RANU for SQL Server Express.

ENABLERANU=”False”

; Specifies a Windows collation or an SQL collation to use for the Database Engine.

SQLCOLLATION=”SQL_Latin1_General_CP1_CI_AS”

; Account for SQL Server service: Domain\User or system account.

SQLSVCACCOUNT=”NT Service\MSSQL$SVR2″

; Windows account(s) to provision as SQL Server system administrators.

SQLSYSADMINACCOUNTS=”mydomain\myusername” “mypassword”

; The default is Windows Authentication. Use “SQL” for Mixed Mode Authentication.

SECURITYMODE=”SQL”

; The Database Engine root data directory.

INSTALLSQLDATADIR=”D:\Program Files\Microsoft SQL Server”

; Default directory for the Database Engine backup files.

SQLBACKUPDIR=”M:\Program Files\Microsoft SQL Server\MSSQL11.SVR2\MSSQL\Backup”

; Default directory for the Database Engine user database logs.

SQLUSERDBLOGDIR=”M:\Program Files\Microsoft SQL Server\MSSQL11.SVR2\MSSQL\Data”

; Directory for the Database Engine TempDB log files.

SQLTEMPDBLOGDIR=”M:\Program Files\Microsoft SQL Server\MSSQL11.SVR2\MSSQL\Data”

; Provision current user as a Database Engine system administrator for SQL Server 2012 Express.

ADDCURRENTUSERASSQLADMIN=”False”

; Specify 0 to disable or 1 to enable the TCP/IP protocol.

TCPENABLED=”0″

; Specify 0 to disable or 1 to enable the Named Pipes protocol.

NPENABLED=”0″

; Startup type for Browser Service.

BROWSERSVCSTARTUPTYPE=”Automatic”

; Add description of input argument FTSVCACCOUNT

FTSVCACCOUNT=”NT Service\MSSQLFDLauncher$SVR2″

 

Creating A Contained Database – SQL 2012/2014

“A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.

  • Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
  • All metadata are defined using the same collation.
  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
  • The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.”

MSDN Source reference – click here

T-SQL Code

EXEC sys.sp_configure N’contained database authentication’, N’1′

GO

RECONFIGURE WITH OVERRIDE

GO

USE [master]

GO

ALTER DATABASE [MyDemoDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT

GO

 

SQL Server 2014 Upgrade Advisor

Running the SQL Server 2014 upgrade advisor is a good idea when say planning an in-place upgrade from a previous version of SQL Server.

To do this, run setup from the installation disc, go to planning, run install upgrade advisor.

You might get a pop up such as below, this is resolved by downloading and installing the SQL 2014 feature pack components from this link. Click here.

sql2014_needs