1

References :
- Pro SQL Server 2019 Administration_ A Guide for the Modern [Link] (Chapter
10)
- [Link]

SQL Server Security Model

SQL Server 2019 offers a complex security model with overlapping layers of security that
help database administrators (DBAs) counter the risks and threats in a manageable way.
It is important for DBAs to understand the SQL Server security model so that they can
implement the technologies in the way that best fits the needs of their organization and
applications.

Security Hierarchy
The security hierarchy for SQL Server begins at the Windows domain level and cascades
down through the local server, the SQL Server instance, the databases, and right on down to
the object level.
The model is based on the concept of principals, securables, and permissions.
Principals are entities to which permissions are granted, denied, or revoked.
Revoking a permission means deleting an existing grant or denying assignment.
Groups and roles are principals that contain zero or more security principals and simplify the
management of security by allowing you to assign permissions to similar principals as a
single unit.
Securables are objects that can have permissions granted on them—for example, an endpoint
at the instance level, or a table within a database. Therefore, you grant a permission on a
securable to a principal.
Permissions - Every SQL Server securable has associated permissions that can be granted to
a principal. Permissions in the Database Engine are managed at the server level assigned to
logins and server roles, and at the database level assigned to database users and database
roles.
 2

The diagram shows that a login, created within the SQL Server instance, can be mapped to a
local Windows user or group or to a domain user or group (Enterprise environment)
When added to the domain group called SalesTeam—which already has all of the required
permissions to file system locations, SQL Server databases, and so on—he immediately
inherits all required permissions to perform his role.
The diagram also illustrates how local server accounts or domain accounts and groups can be
mapped to a user at the database level (a database user without login). This is part of the
functionality of contained databases.
 3

You can then add the Windows login, which you create at the SQL Server instance level or at
a second tier SQL Server login (if you are using mixed-mode authentication), to fixed server
roles and user-defined server roles at the instance level. Doing this allows you to grant the
user common sets of permissions to instance-level objects, such as linked servers and
endpoints.
You can also map logins to database users. Database users sit at the database level of the
hierarchy. You can grant them permissions directly on schemas and objects within the
database, or you can add them to database roles.
Database roles are similar to server roles, except they are granted a common set of
permissions on objects that sit inside the database, such as schemas, tables, views, stored
procedures, and so on.

Implementing Instance-Level Security

Unless you are using contained databases (discussed later in this chapter), all users must be
authenticated at the instance level.
You can use two authentication modes with SQL Server: Windows authentication and mixed-
mode authentication.
When you select Windows authentication, a login at the instance level is created and mapped
to a Windows user or group, which exists either at the domain level or at the local server
level.
When you create a login mapped to a Windows user or group, SQL Server records the SID
(security identifier) of this principal and stores it in the Master database. It then uses this SID
to identify users who are attempting to connect, from the context that they have used, to log
in to the server or domain.
In addition to creating a login mapped to a Windows user or group, you can also map a login
to a certificate or an asymmetric key.
When you use second tier authentication,you create a SQL login, which has a username and
password. This username and password is stored in the Master database with its own SID.
When you are using mixed-mode authentication, there will be a special user, called the SA.
This is the System Administrator account, and it has administrative rights to the entire
instance.
You can rename the SA account by using the command :
ALTER LOGIN sa WITH NAME = PROSQLADMINSA ;

Server Roles
 4

SQL Server provides a set of server roles, out of the box, that allow you to assign instance-
level permissions to logins that map to common requirements. These are called fixed
server roles.
Fixed server- Description
level role
sysadmin Perform any activity in the server.
serveradmin Change server-wide configuration options, shut down the server.
securityadmin - Manage logins and their properties.
- GRANT, DENY, and REVOKE server-level permissions.
- GRANT, DENY, and REVOKE database-level
- Reset passwords for SQL Server logins.
processadmin End processes running in an instance of SQL Server.
setupadmin Add and remove linked servers
bulkadmin Members of the bulkadmin fixed server role can run the BULK
INSERT statement.
diskadmin Managing disk files.
dbcreator Create, alter, drop, and restore any database.
public Every SQL Server login belongs to the public server role.
When a server principal has not been granted or denied specific
permissions on a securable object, the user inherits the permissions
granted to public on that object.
Only assign public permissions on any object when you want the object
to be available to all users. You cannot change membership in public.
 5

You can also create your own server roles, which group users who need a common set of
permissions. You can create this server role by selecting New Server Role from the context
menu of Security ➤ Server Roles in SSMS.

On the Members tab of the dialog box, we can search for preexisting logins that we will add
to the role,
In the Membership tab, we can optionally choose to nest the role inside another server role.
Server-Level Permissions :
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
 6

Logins
You can create a login through SSMS or through T-SQL. To create a login through SSMS,
select New Login from the context menu of Security ➤ Logins.

We have also set the login’s default database. This does not assign the user any permissions
to default database, but it specifies that this database will be the login’s landing zone, when
the user authenticate to the instance. It also means that if the user does not have permissions
to default database, or if default database is dropped or becomes inaccessible, the user will
not be able to log in to the instance.
On the Server Roles tab, you can add the login to server roles.
On the User Mapping tab, we can map the login to users at the database level.
On the User Mapping tab, we can map the login to users at the database level.
 7

On the Securables tab, we can search for specific instance-level objects on which to grant the
login permissions.
In the Status tab, we can grant or deny the login permissions to log in to the instance and
enable or disable the login.

Granting Permissions
When assigning permissions to logins, you can use the following actions:
 GRANT
 DENY
 REVOKE

GRANT gives principal permissions on a securable. You can use the WITH option with
GRANT to also provide a principal with the ability to assign the same permission to other
principals.
DENY specifically denies login permissions on a securable; DENY overrules GRANT.
REVOKE removes a permission association to a securable. This includes DENY associations
as well as GRANT associations.
Granting and Denying Permissions :
GRANT ALTER ANY LOGIN TO Danielle ;
GO
DENY ALTER ON LOGIN::[NT Service\MSSQL$PROSQLADMIN] TO Danielle ;

We can add or remove logins from a server role by using the ALTER SERVER ROLE
statement.
--Add Danielle to the sysadmin Role
ALTER SERVER ROLE sysadmin ADD MEMBER Danielle ;
GO
--Remove Danielle from the sysadmin role
ALTER SERVER ROLE sysadmin DROP MEMBER Danielle ;
GO

Implementing Database-Level Security

Security at the level of the individual database include database users and database roles.

Database Roles
Database roles at the database level that can group principals together to assign common
permissions. There are built-in database roles, but it is also possible to define your own, ones
that meet the requirements of your specific data-tier application.
 8

Fixed-Database Description
role name
db_accessadmin Remove database users.
db_backupoperator Back up the database.
db_datareader Run SELECT statements against any table in the database.
db_datawriter Perform DML (Data Manipulation Language) statements against any
table in the database.
db_denydatareader Denies the SELECT permission against
every table in the database.
db_denydatawriter Denies its members the permissions to
perform DML statements against every table in the database.
db_ddladmin Run CREATE, ALTER,
and DROP statements against any object in the database.
db_owner Perform any action within the
database that has not been specifically denied.
db_securityadmin Grant, deny, and revoke a user’s permissions to securables.
Add or remove role memberships, with the exception of the db_owner
role.

Create your own database roles in SQL Server Management Studio by drilling down through
Databases ➤ Your Database ➤ Security and then selecting New Database Role from the
context menu of database roles in Object Explorer.
On the Securables tab, we can search for objects that we want to grant permissions on, and
then we can select the appropriate permissions for the objects.
 9

An alternative way to create database role is to use the T-SQL script :

--Set Up the Role
CREATE ROLE db_ReadOnlyUsers AUTHORIZATION dbo ;
GO
ALTER ROLE db_ReadOnlyUsers ADD MEMBER Danielle ;
GRANT SELECT ON [Link] TO db_ReadOnlyUsers ;
DENY DELETE ON [Link] TO db_ReadOnlyUsers ;
DENY INSERT ON [Link] TO db_ReadOnlyUsers ;
DENY UPDATE ON [Link] TO db_ReadOnlyUsers ;
GO

Schemas
Schemas provide a logical namespace for database objects while at the same time abstracting
an object from its owner.
 10

This abstraction simplifies changing the ownership of database objects; in this example, to
change the owner of the ten tables from Bob to Colin, you need to change the ownership in
one single place (the schema) as opposed to changing it on all ten tables.
Well-defined schemas can also help simplify the management of permissions,because you
can assign principal permissions on a schema, as opposed to the individual objects within that
schema. For this reason, well-designed schemas group tables by business rules.

A good schema design for this example would involve three schemas, which are split by
business responsibility—Sales, Procurement, and Accounts.
Demonstrates how these tables can be split and permissions can then be assigned to the tables
via database roles.
 11

Creating and granting Permissions on a Schema.

CREATE SCHEMA CH10 ;
GO
GRANT SELECT ON SCHEMA::CH10 TO Danielle ;

Creates a table without specifying a schema. This means that it is automatically placed in the
dbo schema. It is then moved to the CH10 schema.
CREATE SCHEMA CH10 ;
GO

GRANT SELECT ON SCHEMA::CH10 TO Danielle ;

Creating and Managing Contained Users

Create a database user, which maps to a login at the instance level.
It is also possible to create a database user, which does not map to a server principal,
however. This is to support a technology called contained databases.
Contained databases allow you to reduce a database’s dependency on the instance by
isolating aspects such as security. This makes the database easier to move between instances
and helps support technologies such as AlwaysOn Availability Groups.
SQL Server supports the database containment levels of NONE, which is the default, and
PARTIAL. PARTIAL indicates that the database supports contained database users and that
metadata is stored inside the database using the same collation (ref : CREATE DATABASE).
However, the database can still interact with no-contained features, such as users mapped to
logins at the instance level.
In order to use contained databases, you must enable them at both the instance and the
database level.
You can enable them at both levels by using the Server Properties and Database Properties
dialog boxes in SQL Server Management Studio.
 12

Alternatively, you can enable them at the instance level by using sp_configure and at the
database level by using the ALTER DATABASE statement.
--Enable contained databases at the instance level
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
--contained database authentication is on (1) for the instance, contained
databases can be created
EXEC sp_configure 'contained database authentication', '1' ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
--Set TEST database to use partial containment
USE Master
GO
CREATE DATABASE TEST ;
GO
ALTER DATABASE TEST SET CONTAINMENT = PARTIAL WITH NO_WAIT ;
GO

Once you have enabled contained databases, you can create database users that are not
associated with a login at the instance level.
Creating a Database User from a Windows Login :
USE TEST
GO
CREATE USER [HTTT1910\TestUser] WITH DEFAULT_SCHEMA=dbo ;
GO

Alternatively, to create a database user who is not mapped to a login at the instance level but
who still relies on second tier authentication.
USE TEST
GO
CREATE USER ContainedUser WITH PASSWORD=N'Pa$$w0rd', DEFAULT_SCHEMA=dbo ;
GO

Some applications may require that a user have permissions to multiple databases. If the user
is mapped to a Windows user or group, then this is straightforward because the SID that is
being authenticated is that of the Windows object. If the database user is using second tier
authentication, however, then it is possible to duplicate the SID of the user from the first
database.
USE Master
GO
CREATE DATABASE TEST2 ;
GO
ALTER DATABASE TEST2 SET CONTAINMENT = PARTIAL WITH NO_WAIT ;
GO
USE TEST2
GO
CREATE USER ContainedUser WITH PASSWORD = 'Pa$$w0rd',
SID = 0x01050000000000090300000063EECAFED8FAD24197109FA4CB43B588;

Determine the SID by querying the sys.database_principals catalog :

 13

USE TEST
GO
SELECT sid
FROM sys.database_principals
WHERE name = 'ContainedUser' ;

Once we have duplicated the user in the second database, we also need to turn on the
TRUSTWORTHY property of the first database in order to allow cross-database queries to
take place.
ALTER DATABASE TEST SET TRUSTWORTHY ON ;

Implementing Object-Level Security

Many permissions can be granted and not all permissions are relevant to each object. For
example, the SELECT permission can be granted on a table or a view, but not to a stored
procedure. The EXECUTE permission, on the other hand, can be granted on a stored
procedure, but not to a table or view.
There are two variations of syntax for granting a database user permissions to an object.
The first uses the OBJECT phrase, whereas the second does not.
--Grant with OBJECT notation
USE KETOAN
GRANT SELECT ON OBJECT::[Link] TO [tqt] ;
GO

--Grant without OBJECT notation

USE KETOAN
GRANT SELECT ON [Link] TO [tqt] ;
GO

When granting permissions on a table, it is possible to grant permissions to specific

columns, as opposed to the table itself.
Refer to permissions at [Link]
databases/security/permissions-database-engine?view=sql-server-ver15
14