Manage Logins and Server Roles

Question 1

What is the difference between a login and a user?

Answer 1

A LOGIN is needed to enter the world of SQL server

A USER is needed to connect to a specific Database

Question 2

What is the t-sql syntax for creating a Windows login account?

Answer 2

USE MASTER
GO
CREATE LOGIN [USERNAME] FROM WINDOWS WITH DEFAULT_DATABASE=[DATABASENAME]

Question 3

What are the default server roles?

Answer 3

BULKADMIN
DBCREATOR
DISKADMIN
PROCESSADMIN
PUBLIC
SECURITYADMIN
SERVERADMIN
SETUPADMIN
SYSADMIN

Question 4

What can the SYSADMIN server role do?

Answer 4

All permissions on the server

Question 5

What can the BULKADMIN server role do?

Answer 5

Administer bulk operations (bulk inserts)

Question 6

What can DBCREATOR server role do?

Answer 6

Alter any database, create any database

Question 7

What can SETUPADMIN server role do?

Answer 7

Alter any linked server

Question 8

What can SECURITYADMIN server role do?

Answer 8

Alter any login. Securityadmin should be considered equivalent of SYSADMIN role.

Question 9

What can DISKADMIN server role do?

Answer 9

Alter Resources

Question 10

What can SERVERADMIN server role do?

Answer 10

Alter Resources
Alter Settings
Shutdown
Alter any endpoint
Create Endpoint
Alter Server State
View Server State

Question 11

What can PROCESSADMIN server role do?

Answer 11

Alter Server State
View Server State
Alter Any Connection

Question 12

What can PUBLIC server role do?

Answer 12

No permissions inherent, View any database and connect permission to the endpoints is default, but can be revoked.

Question 13

What is the t-sql syntax for granting a server role to a login?

Answer 13

ALTER SERVER ROLE [SYSADMIN] ADD MEMBER [LOGINNAME]

Question 14

What are the three permissions that you can give?

Answer 14

GRANT
WITH GRANT
DENY

Question 15

What does the WITH GRANT permission mean?

Answer 15

It means that the user can grant that permission to another login

Question 16

What is the t-sql syntax to grant a permission to a server role (IE: ALTER ANY LOGIN to SERVERROLE)

Answer 16

GRANT ALTER ANY LOGIN TO [SERVERROLE]

Question 17

What is the difference between explicit and effective permissions?

Answer 17

Explicit means that the login has that permission specifically for the login
Effective means they have the right because of a role they have been granted.

Question 18

If a login has two roles where one role grants a permission and another role denies the same permission, which one wins?

Answer 18

Deny trumps grant.

Question 19

What is the t-sql syntax for creating a database user?

Answer 19

CREATE USER [USERNAME] FOR LOGIN [SERVERLOGIN] WITH DEFAULT_SCHEMA=[SCHEMA]

Question 20

What are the Database Level Roles?

Answer 20

DB_OWNER
DB_DATAREADER
DB_DATAWRITER
DB_DENYDATAWRITER
DB_ACCESSADMIN
DB_SECURITYADMIN
DB_BACKUPOPERATOR
DB_DDLADMIN
PUBLIC

Question 21

What are the permissions for DB_DATAREADER?

Answer 21

GRANT SELECT ON DATABASE::

Question 22

What are the permissions for DB_DENYDATAREADER?

Answer 22

DENY SELECT ON DATABASE::

Question 23

What are the permissions for DB_DATAWRITER?

Answer 23

GRANT INSERT ON DATABASE::
GRANT UPDATE ON DATABASE::
GRANT DELETE ON DATABASE::

Question 24

What are the permissions for DB_DENYDATAWRITER?

Answer 24

DENY INSERT ON DATABASE::
DENY UPDATE ON DATABASE::
DENY DELETE ON DATABASE::

Question 25

What are the permissions for DB_ACCESSADMIN?

Answer 25

CREATE SCHEMA
ALTER ANY USER
CONNECT

Question 26

What are the permissions for DB_SECURITYADMIN?

Answer 26

CREATE SCHEMA
ALTER ANY ROLE, CREATE ROLE
ALTER ANY APPLICATION ROLE
VIEW DEFINITION

Question 27

What are the permissions for DB_BACKUPOPERATOR?

Answer 27

BACKUP DATABASE
BACKUP LOG
CHECKPOINT

Question 28

What are the permissions for DB_DDLADMIN?

Answer 28

ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
ALTER ANY FULLTEXT CATALOG
ALTER ANY MESSAGE TYPE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY SCHEMA
ALTER ANY SERVICE
ALTER ANY SYMMETRIC KEY
CHECKPOINT
CREATE AGGREGATE
CREATE DEFAULT 
CREATE FUNCTION
CREATE PROCEDURE
CREATE QUEUE
CREATE RULE
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
REFERENCES

Question 29

What are the permissions for PUBLIC?

Answer 29

No permissions inherent, view column master key definition, view any column encryption key definition, and select permissions on many indicidual system tables which can be revoked.

Question 30

What is the t-sql syntax to add a database user to a role?

Answer 30

USE [DATABASE]
GO
ALTER ROLE [DBROLE] ADD MEMBER [USERNAME]

Question 31

What is the t-sql syntax to create a database role?

Answer 31

USE [DATABASE]
GO
CREATE ROLE [ROLENAME]
GO

Question 32

What is the syntax to give certain access to a role (IE Select access to table DBO.TABLE1 to ROLE1)?

Answer 32

GRANT SELECT ON [DBO].[TABLE1] TO [ROLE1]

Question 33

What do REFERENCES refer to within securables?

Answer 33

Foreign Key constraints

Question 34

What does ALTER refer to within securables?

Answer 34

Ability to change permissions to a securable, but can’t change ownership

Question 35

What does CONTROL refer to within securables?

Answer 35

That’s the ownership (all permissions possible on securable)

Question 36

What is the t-sql syntax for creating schema MySchema with User1 as the owner?

Answer 36

CREATE SCHEMA [MYSCHEMA] AUTHORIZATION [USER1]

Question 37

What is Ownership Chaining?

Answer 37

If a user is granted access to a Procedure or Function that relies on a table or view, the system does not check permissions as long as the schema owner is the same.

Question 38

How do you create access to Server/Database with least privilege?

Answer 38

Use Fixed Server Roles
Restrict Use of SYSADMIN
Assign Permissions to roles
use stored procedures and functions

Question 39

How do you remove the grant / deny access to a user?

Answer 39

REVOKE [security] ON [table] TO [User/Role]