SQL DBA Interview Questions and Answers – Security Permissions
1) What is Authentication and Authorization? What is the difference between both?
Authentication is the process of verifying who you are. Logging on to a PC with a username and password is authentication.
Authorization is the process of verifying that you have access to something. Authorization is gaining access to a resource (e.g. directory on a hard disk) because the permissions configured on it allow you to access it.
2) How many type of SQL Server authentication mode supported by SQL Server 2012?
There are two type of authentication available in SQL Server.
Windows Authentication — TRUSTED connection
Windows Logins
Windows Groups
MIXED authentication — NON Trusted connection
Windows Logins
Windows Groups
SQL Server logins
3) What’s the difference between Windows and Mixed mode?
Windows authentication mode requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.
Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server
4) Being a DBA which authentication mode you will prefer if you are asked to give an advice for a new Application?
Windows authentication is definitely more secure as it’s controlled and authenticated by Active Directory policies.
5) What are Principals?
Principals are entities that can request SQL Server resources. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID). e.g.
Windows-level principals
• Windows Domain Login
• Windows Local Login
SQL Server-level principals
• SQL Server Login
• Server Role
Database-level principals
• Database User
• Database Role
• Application Role
6) What is a Securable?
Securables are the resources to which the SQL Server Database Engine authorization system regulates access. For example, a table is a securable. Some securables can be contained within others, creating nested hierarchies called “scopes” that can themselves be secured. The securable scopes are server, database, and schema.
7) Explain scope of securable on Server, Database and Schema level?
Securable scope: Server —The server securable scope contains the following securables:
• Endpoint
• Login
• Server role
• Database
Securable scope: Database —The database securable scope contains the following securables:
• User
• Database role
• Application role
• Assembly
• Message type
• Route
• Service
• Remote Service Binding
• Full text catalog
• Certificate
• Asymmetric key
• Symmetric key
• Contract
• Schema
Securable scope: Schema —The schema securable scope contains the following securables:
• Type
• XML schema collection
• Object – The object class has the following members:
o Aggregate
o Function
o Procedure
o Queue
o Synonym
o Table
o View
8) What are logins and users and its difference?
A login is the principal that is used to connect to the SQL Server instance. A user is the principal that is used to connect to a database.
The security context on the instance itself is dictated by the login, it’s roles and the permissions granted/denied. The security context on the database is dictated by the user, it’s roles and the permissions granted/denied.
9) What is a schema?
SQL Server 2005 introduced the concept of database schemas and the separation between database objects and ownership by users. An object owned by a database user is no longer tied to that user. The object now belongs to a schema – a container that can hold many database objects. schema as a collection of database objects that are owned by a single principal and form a single namespace
10) What are Fixed Server roles and importance?
Bulk Admin: Members of this role can perform Bulk Insert operations on all the databases.
DBCreator: Members of this role can Create/Alter/Drop/Restore a database.
Disk Admin: Members can manage disk files for the server and all databases. They can handle backup devices.
Process Admin: Members of this role can manage and terminate the processes on the SQL Server.
Server Admin: Members of this role can change Server-wide configurations and shutdown SQL Server instance.
Setup Admin: Members of this role can Add/Remove Linked Servers.
Security Admin: Members of this role can create/manage Logins, including changing and resetting passwords as needed, and managing GRANT, REVOKE and DENY permissions at the server and database levels.
SysAdmin: Members of this role have Full Control on the instance and can perform any task.
Public: Public is another role just like Fixed Server Roles, that is by default granted to every login (Windows/SQL)
11) What are “View Server State”,”VIEW DATABASE STATE” permissions meant for?
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
12) What are “View Definition” permissions?
The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.
The VIEW DEFINITION permission can be granted on the following levels:
• Server scope
• Database scope
• Schema scope
• Individual entities
13) What is a guest account?
Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission. The recommendation is not valid for master, msdb and tempdb system databases. If Guest user is disabled in msdb system database, it may cause some issues. Distribution database is also system database and more information about the Guest User in distribution database can be found below. It is recommended to disable guest user in every database as a best practice for securing the SQL Server.
14) Is it possible to create new User Defined Server role in 2012 or not?
Yes, it is possible to create a Server role in SQL Server 2012.
15) What are the security related catalog views?
Server-Level Views
sys.server_permissions
sys.sql_logins
sys.server_principals
sys.server_role_members
Database-Level Views
sys.database_permissions
sys.database_role_members
sys.database_principals
16) What are the extra DB roles available in msdb?
SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:
• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole
17) Which one is highest privileged role out of SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole?
SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole andSQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the serve.
18) What are Fixed Database Roles?
db_datareader: The db_datareader role has the ability to run a SELECT statement against any table or view in the database.
db_datawriter: The db_datawriter role has the ability to modify via INSERT, UPDATE, or DELETE data in any table or view in the database.
db_denydatareader: The db_denydatareader role is the exact opposite of the db_datareader role: instead of granting SELECT permissions on any database object, the db_denydatareader denies SELECT permissions.
db_denydatawriter: db_denydatawriter role serves to restrict permissions on a given database. With this role, the user is preventing from modifying the data on any data via an INSERT, UPDATE, or DELETE statement
db_accessadmin: The db_accessadmin fixed database role is akin to the securityadmin fixed server role: it has the ability to add and remove users to the database.
The db_accessadmin role does not, however, have the ability to create or remove database roles, nor does it have the ability to manage permissions.
Granted with GRANT option: CONNECT
db_securityadmin: The db_securityadmin role has rights to handle all permissions within a database. The full list is:
DENY, GRANT, REVOKE, sp_addapprole, sp_addgroup, sp_addrole, sp_addrolemember, sp_approlepassword, sp_changegroup, sp_changeobjectowner, sp_dropapprole, sp_dropgroup, sp_droprole, sp_droprolemember
The list includes the DENY, GRANT, and REVOKE commands along with all the store procedures for managing roles.
db_ddladmin: A user with the db_ddladmin fixed database role has rights to issue Data Definition Language (DDL) statements in order to CREATE, DROP, or ALTER objects in the database.
db_backupoperator: db_backupoperator has rights to create backups of a database. Restore permissions are not granted, but only backups can be performed.
db_owner: Equal to a sysadmin at instance level, DB_OWNER can perform any task at DB Level.
public: By default all the users in database level are granted Public Role.
19) What is the purpose of db_denydatawriter and db_denydatareader?
The deny roles are an extra safeguard that you can use to make sure that certain logins or groups will never have the type of access that is specifically denied under the role. With nesting of groups in Windows and multiple role assignments sometimes individuals inadvertently end up with excessive permissions. This is just another level that can be applied to lessen these accidental cracks in the permissions hierarchy.
20) What are Application Roles?
An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.
21) What are Orphaned Users?
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.
• A database user can become orphaned if the corresponding SQL Server login is dropped.
• A database user can become orphaned after a database is restored or attached to a different instance of SQL Server.
• Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.
22) How to troubleshoot issues with the Orphaned users?
This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
23) How can SQL Server instances be hidden?
To hide an instance of the SQL Server Database Engine
1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then select Properties.
2. On the Flags tab, in the HideInstance box, select Yes, and then click OK to close the dialog box. The change takes effect immediately for new connections.
24) Being a DBA what all measures you will follow to make SQL SERVER more secure?
• When possible, use Windows Authentication logins instead of SQL Server logins
• Using server, database and application roles to control access to the data
• Using an un guessable SA password
• If possible, disable and rename the sa account
• Restricting physical access to the SQL Server
• Disabling the Guest account
• Minimize the number of sysadmins allowed to access SQL Server.
• Give users the least amount of permissions they need to perform their job.
• Use stored procedures or views to allow users to access data instead of letting them directly access tables.
• Don’t grant permissions to the public database role.
• Remove user login IDs who no longer need access to SQL Server.
• Avoid creating network shares on any SQL Server.
• Turn on login auditing so you can see who has succeeded, and failed, to login.
• Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
• Do not use DBO users as application logins
• Firewall restrictions ensure that only the SQL Server listening port is available on the database server.
• Apply the latest security updates / patches
25) What is Transparent Data Encryption?
Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.
26) What is Service master key?
The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.
27) What are the types of keys used in encryption?
Symmetric Key – In Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages.
Asymmetric Key – Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key – to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it. This intricacy has turned it into a resource-intensive process.
28) How to take backup of the Service master key?
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' ENCRYPTION BY PASSWORD = 'password'
29) Is it possible to disable SA, how?
Disable the SA Login
Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled. The only disadvantage is that we can’t use the SA account in an emergency. we can use the below T-SQL to disable SA account.
–Query to disable the SA account.
ALTER LOGIN sa DISABLE;
30) Is it possible to Rename the SA Login
Yes we can rename the SA account which will prevent hackers/users to some extent.
–Query to check account status
ALTER LOGIN sa WITH NAME = [newname];
31) Define SQL Server Surface Area Configuration Tool
SQL Server 2005 contains configuration tools such as a system stored procedure calledsp_configure or SQL Server Surface Area Configuration tool (for services and features) in order to enable/disable optional features as needed. Those features are usually installed as disabled by default. Here is the list of the features that can be enabled using the tool:
• xp_cmdshell
• SQL Server Web Assistant
• CLR Integration
• Ad hoc remote queries (the OPENROWSET and OPENDATASOURCE functions)
• OLE Automation system procedures
• System procedures for Database Mail and SQL Mail
• Remote use of a dedicated administrator connection
