SQLServerCh18

Chapter 18

How to manage
database security

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Applied
 Given the specifications for a new user’s security permissions,
write the Transact-SQL statements that create the new user and
grant the security permissions.
 Given the specifications for a new user’s security permissions, use
the Management Studio to create the new user and grant the
security permissions.
 Given the specifications for a set of security permissions for a
database, write the Transact-SQL statements to create a new
database role and assign users or groups to it.
 Given the specifications for a set of security permissions for a
server, write the Transact-SQL statements to create a new server
role and assign logins to it.

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Given the specifications for a set of security permissions, use the
Management Studio to create a new role and assign users or
groups to it.
 Use SQL statements or the Management Studio to add users to the
fixed server roles or the fixed database roles.
Knowledge
 Identify the two ways that SQL Server can authenticate a login
ID.
 Identify the two SQL Server authentication modes.
 Describe these terms: principals and securables.
 Describe the difference between an object permission and a
database permission.
 Describe the guidelines for a strong password.

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 3

Objectives (cont.)
 Describe what a user can do when given any of the standard
permissions for a SQL Server object: Select, Update, Insert,
Delete, Execute, References, and ALTER.
 Describe the difference between a denied permission and a
revoked permission.
 Describe the two types of fixed roles provided by SQL Server:
fixed server roles and fixed database roles.
 Describe the use of application roles.

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 4

How users gain access to a SQL Server database
Database
Users

Connection and Login
Windows authentication
(uses Windows login ID)

User

Security
Permission

Schema
Object

Or
Group

User

Murach's SQL Server 2012, C18

SQL Server authentication
(uses SQL Server login ID)

Role Permission

© 2012, Mike Murach & Associates, Inc.

Specific SQL
DDL statement

Slide 5

Two ways to configure SQL Server security
 Transact-SQL
 Management Studio

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 6

The dialog box for changed authentication mode

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 7

The two SQL Server authentication modes
Mode
Windows Authentication
mode
Mixed mode

Murach's SQL Server 2012, C18

Description
Only Windows authentication is allowed.
This is the default.
Both modes are allowed. If your database
needs to be accessed by non-Windows
clients, you must use Mixed mode.

© 2012, Mike Murach & Associates, Inc.

Slide 8

The syntax of the CREATE LOGIN statement
for Windows authentication
CREATE LOGIN login_name FROM WINDOWS
[WITH [DEFAULT_DATABASE = database]
[, DEFAULT_LANGUAGE = language]]

Create a new login ID from a Windows account
CREATE LOGIN [Accounting\SusanRoberts] FROM WINDOWS;

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 9

The syntax for the CREATE LOGIN statement
for SQL Server authentication
CREATE LOGIN login_name WITH PASSWORD = 'password'
[MUST_CHANGE]
[, DEFAULT_DATABASE = database]
[, DEFAULT_LANGUAGE = language]
[, CHECK_EXPIRATION = {ON|OFF}]
[, CHECK_POLICY = {ON|OFF}]

Create a new SQL Server login ID
CREATE LOGIN JohnDoe WITH PASSWORD = 'pt8806FG$B',
DEFAULT_DATABASE = AP;

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 10

Guidelines for strong passwords
 Cannot be blank or null or the values “Password”, “Admin”,
“Administrator”, “sa”, or “sysadmin”
 Cannot be the name of the current user or the machine name
 Must contain more than 8 characters
 Must contain at least three of the following: uppercase letters,
lowercase letters, numbers, and non-alphanumeric characters (#,
%, &, etc.)

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 11

Terms
 Login ID
 Permissions
 Role
 Windows group
 Principal
 Securable
 Authentication mode
 Strong password

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 12

The syntax of the DROP LOGIN statement
DROP LOGIN login_name

A statement that deletes a login ID
DROP LOGIN JackWilliams;

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 13

The syntax of the ALTER LOGIN statement
for Windows authentication
ALTER LOGIN login_name {{ENABLE|DISABLE}|WITH
[NAME = login_name]
[, DEFAULT_DATABASE = database]
[, DEFAULT_LANGUAGE = language]}

A statement that disables a Windows login ID
ALTER LOGIN [Accounting\SusanRoberts] DISABLE;

A statement that changes a login name
ALTER LOGIN JohnDoe WITH NAME = JackWilliams;

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 14

The syntax of the ALTER LOGIN statement
for SQL Server authentication
ALTER LOGIN login_name {{ENABLE|DISABLE}|WITH
[PASSWORD = 'password'
[OLD_PASSWORD = 'oldpassword']
[MUST_CHANGE]]
[, NAME = login_name]
[, DEFAULT_DATABASE = database]
[, DEFAULT_LANGUAGE = language]
[, CHECK_EXPIRATION = {ON|OFF}]
[, CHECK_POLICY = {ON|OFF}]}

Change the password for a SQL Server login ID
ALTER LOGIN JohnDoe WITH PASSWORD = 'lg22A%G45x';

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 15

The syntax of the CREATE USER statement
CREATE USER user_name
[{FOR|FROM} LOGIN login_name]
[WITH DEFAULT_SCHEMA = schema_name]

Create a database user with the same name
as a login ID
CREATE USER JohnDoe;

Creates a database user
for a Windows user account
CREATE USER SusanRoberts FOR LOGIN
[Accounting\SusanRoberts];

Create a database user with a default schema
CREATE USER SusanRoberts FOR LOGIN
[Accounting\SusanRoberts]
WITH DEFAULT_SCHEMA = Accounting;

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 16

The syntax of the ALTER USER statement
ALTER USER user_name WITH
[NAME = new_user_name]
[, DEFAULT_SCHEMA = schema_name]

Change a user name
ALTER USER SusanRoberts WITH NAME = SusanStanley;

Assign a default schema to a user
ALTER USER JohnDoe WITH DEFAULT_SCHEMA = Marketing;

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 17

The syntax of the DROP USER statement
DROP USER user_name

A statement that deletes a database user
DROP USER JohnDoe;

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 18

The syntax of the CREATE SCHEMA statement
CREATE SCHEMA schema_name [AUTHORIZATION owner_name]
[table_definition]...
[view_definition]...
[grant_statement]...
[revoke_statement]...
[deny_statement]...

Create a schema
CREATE SCHEMA Accounting;

Create a schema and a table within that schema
CREATE SCHEMA Marketing
CREATE TABLE Contacts
(ContactID
INT

NOT NULL IDENTITY
PRIMARY KEY,
ContactName VARCHAR(50) NOT NULL,
ContactPhone VARCHAR(50) NULL,
ContactEmail VARCHAR(50) NULL);

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 19

The syntax of the ALTER SCHEMA statement
ALTER SCHEMA schema_name TRANSFER securable_name

Transfer a table from one schema to another
ALTER SCHEMA Accounting TRANSFER Marketing.Contacts;

Notes
 You can’t transfer an object from one schema to another if any
views or functions are bound to it.
 When you transfer an object from one schema to another, all the
permissions that were associated with that object are dropped.

Murach's SQL Server 2012, C18

© 2012, Mike Murach & Associates, Inc.

Slide 20