Archive for the ‘SQL Server’ Category

TSQL: Database Permission Exercise for 70-462

Here’s the TSQL for an exercise, involving database permissions, from the 70-462. Explanatory comments are included. CREATE 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 ( id INTEGER IDENTITY(1,1) NOT […]

TSQL: Create SQL Logins using certificates and asymmetric keys 70-462

Here’s some TSQL for creating sql logins using certificates and asymmetric keys. Explanatory comments are included. CREATE CERTIFICATE myCert ENCRYPTION BY PASSWORD = ‘Secret2014’ WITH SUBJECT = ‘My certificate for sql logins’; USE [master] GO — create sql user with the cert CREATE LOGIN [sql_user_a] FROM CERTIFICATE [myCert]; GO — create key CREATE ASYMMETRIC KEY […]

TSQL: Partially Contained Databases 70-462

Here’s some TSQL for the Partially Contained Databases section of the 70-462. Explanatory comments are included. — 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; GO — […]

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. USE [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 ROLE [Login_Manager] ADD MEMBER [CONTSO\domain_group_b]; […]

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… EXEC sp_helpsrvrole; List the members of a role… EXEC sp_helpsrvrolemember ‘sysadmin’; List the permissions assigned to a server-level role… EXEC sp_srvrolepermission ‘sysadmin’; Test is a login is the member […]