Archive for the ‘T-SQL’ Category

TSQL: Enable & Disable Logins and DENY connect

Warning: count(): Parameter must be an array or an object that implements Countable in /home/fbsqlcom/public_html/ on line 31

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… ?View Code TSQLUSE [master] GO DENY CONNECT SQL TO [sql_user_c] GO ALTER LOGIN [sql_user_c] DISABLE GO Grant connect and […]

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. ?View Code TSQLDECLARE @dbname sysname, @days int SET @dbname = NULL –substitute for whatever database name you want   — Max data / restore type […]

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. ?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: 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. ?View Code TSQLCREATE 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   — […]

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