What permissions does the fixed sever role sysadmin have?
Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins. IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin
Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin
Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)
bulkadmin
Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin
The diskadmin fixed server role is used for managing disk files.
dbcreator
Members of the dbcreator fixed server role can 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. Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.
What are the features of the Service Master Key? (4 features)
- The Service Master Key is the root of the database engines encyption hierarchy.
- Generated the first time it is need to encrypt another key.
- By default the Service Management key is encrypted by the Windows data protection API (DPAPI) at the operating system level, which uses the local machine key.
- The Service Management Key can only be opened by the Windows Service Account that created it, or by a principal that knows the service account name and its password.
What are the features of Extensible Key Management (EKM)?
- SQL Server EKM enables encryption keys that protect database files to be stored outside of the SQL Server environment e.g. smartcard, USB, or EKM module of a Hardware Security Module (HSM).
- Helps secure SQL Server instance from database administrators as they will not necessarily have access to the EKM/HSM module.
What are the features of the Database Master Key (DMK)?
- Symmetric key used to protect the private keys of certificates and asymmetric keys present in database.
- Created used AES_246 and a password that is provided.
- Query the sys.symmetric_keys to get information about the DMK.
What are the features of an Asymmetric Key?
- Consists of a private and corresponding public key.
- Asymmetric encryption is more computationally expensive than symmetric encryption.
- Asymmetric encryption is more secure than symmetric encryption.
- An Asymmetric key can be used to encrypt a symmetric key in a database.
What are the features of a Symmetric key?
- A symmetric key is a single key that uses encryption.
- Symmetric encryption is generally used over asymmetric encryption as it is faster and less computationally expensive.
What are the features of certificates for encryption?
- Digitally signed object.
- Contain a public and optionally a private key for SQL Server which can generate certificates.
- Can be used in asymmetric encryption.
Draw the SQL Server encryption hierarchy.
See attached image.
Can encrypted data be compressed?
No. You should compress the data before encrypting.
Which encryption algorithms are supported from SQL Server 2016 onwards?
Starting with SQL Server 2016 the only algorithms that are supported with database
compatibility 130 or above are AES-128, AES_192, and AES_256.
