See below code:
SQLMint
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
Click here for a download link for this useful SQL Server 2012 System Views Map – in PDF and XPS file format(s)
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
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″
Right clicking on a SQL server database using SQL Server Management Studio, click tasks, copy database and follow the wizard.
Below are major screenshots for opening up the primary firewall ports, using the inbound rules node of the windows firewall with advanced security node, in order to achieving this.
Transferring logins and passwords between separate SQL Server instances eg SVR1\DEV to SVR1\DEV2 can be done by scripting out the encrypted passwords using the stored procedures sp_hexadecimal and sp_help_revlogin which can be found by following this detailed link by clicking here.
“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.
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
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.