Archive for the ‘DBA’ Category

Server Roles with TSQL

Here’s a few bits of TSQL you can use when working with Server-Level Roles in SQL Server 2012. List the server roles setup on an instance… ?View Code TSQLEXEC sp_helpsrvrole; List the members of a role… ?View Code TSQLEXEC sp_helpsrvrolemember ‘sysadmin’; List the permissions assigned to a server-level role… ?View Code TSQLEXEC sp_srvrolepermission ‘sysadmin’; Test […]

TSQL: Transparent Data Encryption exercise for 70-462

Here’s some TSQL for the WingTipToys2012 Transparent Data Encryption (TDE) exercise in the 70-462 training materials. ?View Code TSQLCREATE DATABASE WingTipToys2012; GO   USE WingTipToys2012; GO   USE [master]; GO   CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SecretPa$$w0rd’; GO   CREATE CERTIFICATE server_cert WITH SUBJECT = ‘My DEK Certificate’; GO   USE WingTipToys2012; GO […]

TSQL: Partitioned table exercise for 70-462

Here’s some TSQL for the WingTipToys2012 table partitioning exercise in the 70-462 training materials. ?View Code TSQLCREATE DATABASE WingTipToys2012   USE WingTipToys2012; GO   CREATE PARTITION FUNCTION WTPartFunction(INTEGER) AS RANGE LEFT FOR VALUES (30, 60)   CREATE PARTITION SCHEME WTPartScheme AS PARTITION WTPartFunction TO (fgOne, fgTwo, fgThree);   CREATE TABLE toys ( column1 INTEGER NOT […]

Installing Analysis Service & Reporting Services from the command-line

Here’s just a few examples of installing Analysis Services and Reporting Services from the command-line. Install Analysis Services in multidimensional mode. ?View Code DOSD:\Setup.exe /q /ACTION=Install /FEATURES=AS /INSTANCENAME=ASMulti /ASSERVERMODE=MULTIDIMENSIONAL /ASSVCACCOUNT=NetworkService /ASSYSADMINACCOUNTS="CONTSO\Kim_Akers"/IACCEPTSQLSERVERLICENSETERMS Install Analysis Services in tabular mode. ?View Code DOSD:\Setup.exe /q /ACTION=Install /FEATURES=AS /INSTANCENAME=ASTabular /ASSERVERMODE=TABULAR /ASSVCACCOUNT=NetworkService /ASSYSADMINACCOUNTS="CONTSO\Kim_Akers"/IACCEPTSQLSERVERLICENSETERMS Install a Reporting Services instance (with the DB […]

TSQL: Restore a database without a log (ldf) file

I had to restore a bunch of databases missing a log file today. This was only a test server but I wanted to get it running as quickly as possible. The script here is based on the advice in this blog post from sqlskills. Note that this is very dodgy and should only be used […]