Archive for the ‘T-SQL’ Category

TSQL: Enable & Disable Logins and DENY connect

More notes for the 70-462 exam. This time we’re showing examples from ALTER LOGIN to enable and disable logins, as well a denying and granting the connect permission. To Deny connect and disable a sql login… USE [master] GO DENY CONNECT SQL TO [sql_user_c] GO ALTER LOGIN [sql_user_c] DISABLE GO Grant connect and enable a […]

TSQL: When were databases last restored

Here’s a couple of queries I’ve modified from When was the last time your SQL Server database was restored? This first query shows restore times by database and restore type. DECLARE @dbname sysname, @days int SET @dbname = NULL –substitute for whatever database name you want — Max data / restore type SELECT rsh.destination_database_name AS […]

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