Posts Tagged ‘TSQL’

TSQL: Database Permission Excercise for 70-462

Here’s the TSQL for an exercise, involving database permissions, from the 70-462. Explanatory comments are included. ?View Code TSQLCREATE DATABASE Saturn; GO   USE Saturn; GO CREATE ROLE Moon_Table_Editors; GO   — Create tables CREATE TABLE Mimas ( id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY, column1 VARCHAR(100) NOT NULL ); GO CREATE TABLE Thethys ( […]

TSQL: Partially Contained Databases 70-462

Here’s some TSQL for the Partially Contained Databases section of the 70-462. Explanatory comments are included. ?View Code TSQL– enable show advanced options and view current config EXEC sp_configure ‘show advanced options’, 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure; GO — enable the feature EXEC sp_configure ‘contained database authentication’, 1; GO RECONFIGURE WITH OVERRIDE; […]

TSQL: User-Defined Server Roles 70-462

Just a little TSQL for the User-Defined Server Roles exercise in the 70-462 training materials. Explanatory comments are included. ?View Code TSQLUSE [master]; GO   — Create server role CREATE SERVER ROLE [Login_Manager]; GO   — Assign permission to server role GRANT ALTER ANY LOGIN TO [Login_Manager]; GO   — Add login to role ALTER SERVER […]

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: 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 […]