Chapter 17 Textbook Assignment

Purpose: To work with authentication, schemas, permissions and roles

To see video demonstrations from task #1, watch: https://youtu.be/_Z67XXH8Pqk

To see video demonstrations from task #2, watch: https://youtu.be/rwbJhaYt85k

Task #1: Authentication

Scenario: Our boss would like us to begin using mixed authentication for off-site database users and on-site users. They would like us to create a Windows login for SierraAndrews, LucyLing and PeterSullivan (they are going to help with testing the database). The default database should be set to Test (we will need to create the default database)

1. Launch SSMS, create a new query and enter the comment /* Task 1 */

2. Before working on live, production data, we create a test database and copy some tables into it. We will be modifying access to the tables when we define roles.

IMPORTANT: If you think your Examples database has been modified, you should download the following text or zipped file and run it again to recreate the tables BEFORE you run the commands below

a) Download create_examples.zip and decompress the file before running the script

b) Copy/paste the code from create_examples.txt into a new query window.

CREATE DATABASE Test;
GO
USE Test;
GO
SELECT * INTO Employees FROM Examples.dbo.Employees;
SELECT * INTO Departments FROM Examples.dbo.Departments;
SELECT * INTO Customers FROM Examples.dbo.Customers;
SELECT * INTO SalesReps FROM Examples.dbo.SalesReps;
SELECT * INTO SalesTotals FROM Examples.dbo.SalesTotals;
SELECT * INTO Invoices FROM Examples.dbo.Invoices;
SELECT * INTO PaidInvoices FROM Examples.dbo.PaidInvoices;
SELECT * INTO Investors FROM Examples.dbo.Investors;

3. Create a login for SierraAndrews:

CREATE LOGIN SierraAndrews WITH PASSWORD='XYZ12345', DEFAULT_DATABASE=Test;

4. Comment out the code and create logins for LucyLing and PeterSullivan (you decide the passwords)

5. Comment out the code.

Scenario: Sierra, Lucy and Peter have complained that their usernames are too long.

1. We decide to alter their logins and only use their first name or their last name. To be safe, we are going to use a TRY...CATCH block and transaction processing

BEGIN TRY
    BEGIN TRANSACTION
        ALTER LOGIN SierraAndrews WITH NAME=sierra;
        ALTER LOGIN LucyLing WITH NAME=ling;
        ALTER LOGIN PeterSullivan WITH NAME=peter;
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'There was an error altering logins';
    PRINT ERROR_MESSAGE();
END CATCH

2. Comment out the code and save the file as: Chapter17-textbook-assignment.sql

Scenario: Sierra has been moved to a different project and will no longer be testing - we need to remove her from the system

1. Enter the following drop command:

DROP LOGIN sierra;

2. Comment out the code and save your changes.

Task #2 Schemas, Permissions and Roles

Scenario: Our boss would like us to create a schema for sales data and another schema for employee data. They would like the Customers, SalesReps, SalesTotals, Orders and Invoices tables from the Test database to be moved from the dbo schema into the sales schema. They would like the Employees, Investors and Departments tables moved from the dbo schema into the employee schema. Doing this will allow us to set security based on the schema instead of basing it the table and it will save us some time

1. Add a heading of Task #2 and comment it out.

2. Create the Sales and employee schemas

CREATE SCHEMA Sales;
GO
CREATE SCHEMA Employees;

3. Transfer tables from dbo into Sales and then from dbo into Employees

USE Test;
GO
ALTER SCHEMA Sales TRANSFER dbo.SalesReps;
ALTER SCHEMA Sales TRANSFER dbo.SalesTotals;
ALTER SCHEMA Sales TRANSFER dbo.Customers;
ALTER SCHEMA Sales TRANSFER dbo.PaidInvoices;
ALTER SCHEMA Sales TRANSFER dbo.Invoices;

ALTER SCHEMA Employees TRANSFER dbo.Employees;
ALTER SCHEMA Employees TRANSFER dbo.Departments;
ALTER SCHEMA Employees TRANSFER dbo.Investors;

4. To verify that the schemas were changed, expand thet Test database and the Tables folder in SSMS and look at the table names - you will see a prefix of Employees or Sales instead of dbo

5. Comment out the code and save your changes.

Scenario: Our boss would like us to associate the Sales schema with Peter and the Employees schema with Ling

1. To accomplish this, we need to create users for each schema (which is much easier than creating them for each table)

CREATE USER ling WITH DEFAULT_SCHEMA = Employees;
GO
CREATE USER peter WITH DEFAULT_SCHEMA = Sales;

2. Comment out the code and save your changes

Scenario: Our boss would like Peter to have SELECT, INSERT, UPDATE and DELETE priviledges on the Sales schema (that will allow Peter to modify data, but he cannot modify the structure of database objects)

1. To accomplish this, we will use a GRANT command

GRANT SELECT,INSERT,UPDATE, DELETE ON Sales.Customers
TO Peter;

2. We decide this is going to take to long to give permissions at the table level, so we decide to grant permission at the schema level, that way if we add more tables to the schema, Peter will have automatic access to them.

GRANT SELECT,INSERT,UPDATE, DELETE
ON SCHEMA::Sales
TO Peter;

3. Save your changes and comment out the code.

Scenario: Our boss would like to remove the Update and Delete permissions from Peter (apparently, he deleted all the rows in one of the tables and the Test database had to be restored from a backup)

1. To accomplish this, we will use the REVOKE command

REVOKE UPDATE,DELETE
ON SCHEMA::Sales
TO Peter;

2. Save your changes and comment out the code.

Scenario: Our boss would like to give Lucy database level permission to create , modify or remove objects in the Test database

1. Database level permission is higher than schema and lets the user execute DDL commands - this will give Lucy access to the Sales and Employee schemas and all tables within the database. It also lets her create new database objects.

GRANT CREATE TABLE,CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION, CREATE SCHEMA
TO ling;

2. Comment out the code and save your changes

Scenario: Our boss would like us to give Chris Evans access to the Test database with server level priviledges equivalent to the systems administrator

1. First we will create a login for Chris

CREATE LOGIN chris WITH PASSWORD='##LFJ23920', DEFAULT_DATABASE=Test;

2. Next, we will give chris a server role of securityadmin which is equivalent to the sysadmin role

ALTER SERVER ROLE securityadmin ADD MEMBER chris;

3. Comment out the code and save your changes.

Scenario: Our boss would like to know which users are accessing the Test database and what their roles are

1. Enter the following command to view the user's names and their principal_id (new users are at the bottom of the table). You should see ling, peter and chris

SELECT * FROM sys.server_principals;

2. Do not comment out the command, add the command below to show the role for different principal ids

SELECT * FROM sys.server_role_members;

You should notice that chris, principal id 291 has a role of 4 which is securityadmin (you need to look in the first set of results to see the principal_id for chris and the second set of results to determine the assigned role

3. To retrieve information at the database level, enter the two commands shown below:

SELECT * FROM sys.database_principals;

Look for the users we added and take note of the default schema for each (since chris wasn't assigned to a schema, you will only find ling and peter)

4. Save your changes and upload the file to the dropbox in Moodle