Interview Questions.

Question 1. What Is The Guest User Account In Sql Server? What Login Is It Mapped To It?

Answer :

The Guest consumer account is created through default in all databases and is used while explicit permissions are not granted to get right of entry to an object.  It is not mapped immediately to any login, but may be utilized by any login.  Depending in your security desires, it may make feel to drop the Guest user account, in all databases except Master and TempDB

Question 2. What Is The Use Of Builtinadministrators Group In Sql Server?

Answer :

Any Windows login in BUILTINAdministrators organization is through default a SQL Server device administrator. This unmarried institution may be used to manage directors from a Windows and SQL Server perspective

Network Security Interview Questions
Question 3. We Have A List Of three Sql Server Logins Which Are Dedicated To A Critical Application. We Have Given All Required Rights To Those Logins. Now My Question Is We Have To Restrict The Access Only To These Three Logins. Means There Are Two Conditions: A) No Other User Should Be Able To Access The Database Except Those Three Logins B) Even For Those Three Logins They Should Be Able To Run Their Queries Only Through The Application. If Someone Login Through Ssms And Trying To Run A Query Should Result Into A Failure. Finally There Should Be Only Way To Running A Query Is From Their Application Using One Of Those Three Logins, There Should Be No Other Way To Run Queries On That Database. How Do You Restrict?

Answer :

Do not supply access to another login on that database besides for those three app logins.

Create a trigger that take a look at each and each question like underneath

IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’)

raiserror (…..)

Return

Question four. How To Resolve The Orphan Use Problem?

Answer :

To find out the orphan users:

USE <database_name>;

GO;

sp_change_users_login @Action='Report';

GO;

To resolve the orphan user hassle:

10USE <database_name>;

GO

sp_change_users_login @Action='update_one', 

@UserNamePattern='<database_user>',

@LoginName='<login_name>';

GO

Network Security Tutorial
Question five. What Are The Fixed Server Level Roles?

Answer :

SysAdmin – Can carry out any activity

ServerAdmin – Can change server configuration, restart, shutdown server

SecurityAdmin – Can manipulate server degree logins, also can manage db stage in the event that they have permission on db

Granted: ALTER ANY LOGIN

ProcessAdmin – Can kill a system on an instance

Granted: ALTER ANY CONNECTION, ALTER SERVER STATE

DiskAdmin – Can manage the disk files

Granted: ALTER RESOURCES

BulkAdmin – Can carry out BULK INSERT

Granted: ADMINISTER BULK OPERATIONS

SetupAdmin – Can add and eliminate connected servers

Granted: ALTER ANY LINKED SERVER

Dbcreator – Can create, adjust, drop and repair any database on the instance

Granted: CREATE ANY DATABASE

Public – Default function for newly created login

sp_helpsrvrolemember : List out the contributors mapped with the server roles

Internet Security Interview Questions
Question 6. What Are The Database Roles?

Answer :

db_accessadmin – Granted: ALTER ANY USER, CREATE SCHEMA, Granted with Grant choice – Connect
db_backupoperator – Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
db_datareader – Granted – SELECT
db_datawriter – Granted – INSERT, UPDATE and DELETE
db_ddladmin – Granted – Any DDL operation
db_denydatareader – Denied – SELECT
db_denydatawriter – Denied – INSERT, UPDATE and DELETE
db_owner – Granted with GRANT option: CONTROL
db_securityadmin – Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
dbm_monitor – Granted: VIEW most latest fame in Database Mirroring Monitor
sp_helprolemember : List out the contributors mapped with the server roles
Note:

Fixed database roles aren't equal to their database-level permission. For example, the db_owner fixed database function has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does no longer make a consumer a member of the db_owner fixed database position.

Question 7. What Are The Security Related Catalog Views?

Answer :

Server Level:

Sys.Server_permissions
Sys.Server_principals
Sys.Server_role_members
Sys.Sql_logins
Database Level:

Sys.Database_permissions
Sys.Database_principals
Sys.Database_role_members
Internet Security Tutorial Security Testing Interview Questions
Question 8. What Are The Extra Roles Available In Msdb?

Answer :

db_ssisadmin: Equals to sysadmin

db_ssisoperator: Import/Delete/Change Role of own packages

db_ssisltduser: Only can view and execute the packages

dc_admin: Can administrate and use the data collector

dc_operator: Can administrate and use the facts collector

dc_proxy : Can administrate and use the facts collector

PolicyAdministratorRole: can perform all configuration and upkeep activities on Policy-Based Management policies and conditions.

ServerGroupAdministratorRole : Can administrate the registered server organization

ServerGroupReaderRole: Can view and the registered server institution

dbm_monitor: Created inside the msdb database whilst the primary database is registered in Database Mirroring Monitor

Question nine. If You Lose Rights To Your Sql Server Instance What Are The Options To Connect To Sql Server Instance?

Answer :

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTINAdministrators Group

Option3: Change Registry Values

Oracle Security Interview Questions
Question 10. What Objects Does The Fn_my_permissions Function Reports On?

Answer :

SERVER
DATABASE
CHEMA
OBJECT
USER
LOGIN
ROLE
APPLICATION ROLE
TYPE
MESSAGE TYPE
ASYMMETRIC KEY
SYMMETRIC KEY
CERTIFICATE
SERVICE
REMOTE SERVICE BINDING
FULLTEXT CATALOG
ASSEMBLY
CONTRACT
ENDPOINT
ROUTE
XML SCHEMA COLLECTION
SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

SELECT * FROM fn_my_permissions(‘AdventureWorks’, ‘DATABASE’);

SELECT * FROM fn_my_permissions(‘Employee’, ‘OBJECT’)

Security Testing Tutorial
Question 11. Name Three Of The Features Managed By The Surface Area Configuration Tool?

Answer :

Ad-hoc faraway queries

Common language runtime
Dedicated Administrator Connection
Database Mail
Native XML Web Services
OLE Automation
Service Broker
SQL Mail
Web Assistant
xp_cmdshell
Complex SQL Queries Interview Questions
Question 12. What Options Are Available To Audit Login Activity?

Answer :

Custom answer together with your utility to log all logins right into a centralized table

Enable login auditing at the example degree in Management Studio
Execute Profiler to capture logins into the example
Leverage a 3rd birthday celebration product
Network Security Interview Questions
Question 13. How To Perform Backup For Certificates In Sql Server?

Answer :

Using Native Backup
Using Backup Certificate Command
Question 14. Name three Of The Features That The Sql Server Built-in Function Loginproperty Performs On Standard Logins?

Answer :

Date while the password was set

Locked out general login
Expired password
Must exchange password at subsequent login
Count of consecutive failed login tries
Time of the ultimate failed login try
Amount of time for the reason that password coverage has been carried out to the login
Date while the login turned into locked out
Password hash
Question 15. How Can Sql Server Instances Be Hidden?

Answer :

To conceal a SQL Server example, we want to make a trade in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the subsequent: pick the instance of SQL Server, proper click on and pick out Properties. After choosing residences you will simply set Hide Instance to “Yes” and click on OK or Apply. After the trade is made, you want to restart the instance of SQL Server to no longer reveal the call of the instance.

Common SQL Queries Interview Questions
Question sixteen. Is Profiler The Only Tool That Has The Ability To Audit And Identify Ddl Events?

Answer :

No. In SQL Server 2005 DDL triggers had been brought to audit CREATE, ALTER and DROP events for relational (saved tactics, features, perspectives, etc.) and security (certificate, logins, server, and many others.) gadgets.

Question 17. What Are Some Of The Pros And Cons Of Not Dropping The Sql Server Builtinadministrators Group?

Answer :

Pros:

Any Windows login is by default a SQL Server system administrator
This single institution can be used to control SQL Server from a device administrators perspective
Cons:

Any Windows login is via default a SQL Server gadget administrator, which won't be a preferred situation

Sql Server Dba Interview Questions
Question 18. What Is Sql Injection And Why Is It A Problem?

Answer :

SQL Injection is an exploit where unhandledunexpected SQL instructions are surpassed to SQL Server in a malicious way.  It is a hassle because unknowingly statistics can be stolen, deleted, up to date, inserted or corrupted.

Internet Security Interview Questions
Question 19. How Can Sql Injection Be Stopped?

Answer :

DevelopmentDBA:

Validate the SQL commands that are being passed with the aid of the front end
Validate the length and records kind in keeping with parameter
Convert dynamic SQL to saved procedures with parameters
Prevent any instructions from executing with the aggregate of or all the following commands: semi-colon, EXEC, CAST,SET,  dashes, apostrophe, etc.
Based in your front cease programming language determine what unique characters need to be removed earlier than any instructions are surpassed to SQL Server
Network Administration
Prevent traffic from particular IP addresses or domain names
Review the firewall settings to determine if SQL Injection assaults can prevented
Remove old internet pages and directories that are not in use because those may be crawled and exploited
Question 20. How To Recover From Sql Injection?

Answer :

If for a few motive the decision implemented does not solve the hassle and the SQL Injection assault happens again, the fastest path may be to do the following:

Shut down the internet web sites
Review the IIS logs to determine the instructions issued and which web pagecommand has the vulnerability
Convert the code to determine which tables have been affected and the command issued
Find and replace the string on your tables
Correct the internet pagecommand that has the vulnerability
Test to validate the problem not happens
Deploy the internet pagecommand
Re-enable the net websites
Sql Loader Interview Questions
Question 21. How To Enforce Security In Sql Server?

Answer :

By providing strong Passwords, Limited the get admission to to make certain proper humans have get entry to to the proper facts, Creating Customized database roles, server roles and assign privileges and with the aid of selecting the perfect authentication mode and many others.

A DBA must be cautious in imparting security…..General precautions consists of:

Minimize the range of sysadmins allowed to get right of entry to SQL Server.
Give customers the least amount of permissions they want to perform their process.
Use saved strategies or views to allow users to get right of entry to facts instead of letting them immediately get right of entry to tables.
When viable, use Windows Authentication logins instead of SQL Server logins.
Don’t grant permissions to the public database function.
Remove consumer login IDs who not need get admission to 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 at once to the Internet.
Using server, database and application roles to control get entry to to the information
Securing the physical database documents the use of NTFS permissions
Using an un guessable SA password
Restricting physical get right of entry to to the SQL Server
Disabling the Guest account
Isolating SQL Server from the internet server
Choose both of the carrier to run SQL Server (Local User – Not an Admin , Domain User – Not an Admin)
Restrict the far flung management (TC)
If SQL Server authentication is used, the credentials are secured over the network by way of the use of IPSec or SSL, or by installing a database server certificates.
Do not use DBO customers as application logins
Firewall regulations make sure that only the SQL Server listening port is available on the database server.
Remove the SQL visitor consumer account.
Remove the BUILTINAdministrators server login.
Apply the ultra-modern security updates / patches
We have masses of features in SQL SERVER to enforce the security. The fundamental features consist of:

Password guidelines
Encryption
Limited metadata visibility (gadget Tables to Catalog Views)
DDL triggers
User-schema separation
Impersonation
Granular permission units
Security catalog perspectives
In addition to those capabilities we've a few greater delivered in SQL SERVER 2008, like Policy Based Management, Security Audit, Improved Encryption, Backup Security and many others.

When we communicate about the safety we need to don't forget the underneath:

Patches and Updates
Services
Protocols
Accounts
Files and Directories
Shares
Ports
Registry
Auditing and Logging
SQL Server Security
SQL Server Logins, Users, and Roles
SQL Server Database Objects
Question 22. You Are Delegating Permissions On Your Sql Server To Other Administrators. You Have Local, Single Server Jobs On One Server That You Would Like To Allow Another Administer To Start, Stop, And View The History For, But Not Delete History. This Administrator Will Own The Jobs. Which Role Should You Assign?

Answer :

SQLAgentUserRole:

SQL Server offers three constant roles for the agent provider that restriction privileges for administrators. The SQLAgentUserRole is designed for nearby jobs (not multiserver) that permit the member to work with their owned jobs (edit, start, prevent, view records) with out deleting the history of any task.

Question 23. What Is Application Role In Sql Server Database Security?

Answer :

Application roles are database degree roles like database roles. We can create them and assign permissions to them just like ordinary database roles however we will’t map users with them. Instead, we provide a password to unencumber get admission to to the database.

Here it is how it works:

Create a login on SQL Server for application person
Create an application position on the corresponding database.
Give the application role password to the person
User can have get entry to to login to SQL Server but doesn’t have any get admission to to the database which include public role.
He/she just need to offer the password to liberate the get right of entry to to the database
EXEC sp_addapprole ‘App_Role_Name’, ‘Password’
Once it's far done efficaciously the user will get all rights that your app position have on that database.
SQL Server Architect Interview Questions
Question 24. What Are The New Security Features Added In Sql Server 2012?

Answer :

Default Schema for Windows Group Logins: Let’s say we've got a Windows account [MyDomain WinAdmin]. If someone from this organization logged in [MyDomainUser1] and tried to create an object then there can be a brand new schema created like [MyDomainUser1].Table. This trouble got constant in 2012. In 2012 we will assign a default schema for the Windows Group debts.

User Defined Server Roles: Till 2008 R2 we have user described roles at database degree, 2012 allows us to create a Server stage person described roles which offers us greater manage in managing protection.

Contained Database: Easier database migrations because it includes consumer and login data on equal database as opposed to in Master.

Data Protection: Supporting Hash Algorithm-256 (SHA-256) and SHA-512.

Auditing: Native guide/characteristic for auditing the database environment by means of creating the Audit specifications. We also can create user defined audits.

Ex: We can create an Audit specification to hint all occasions for a specific login and write a majority of these occasion info into Audit Log. We also can filter the occasions.

Security Testing Interview Questions
Question 25. What Is The New Security Features Added In Sql Server 2014?

Answer :

Functionality Enhancement for TDE: In 2014 Transparent Data Encryption takes the ordinary backup and then applies the Encryption earlier than writing it to the disk. It allows backup compression is useful while TDE enabled. TDE applies on compressed backup.

CONNECT ANY DATABASE: This is a brand new server level permission that could allow a login to attach all present and destiny databases in the example. This may be helpful when we want to give permissions for audit cause.

IMPERSONATE ANY LOGIN: This is a brand new server level permission which gives us more control in giving/denying impersonate get entry to to logins.

SELECT ALL USER SECURABLES: A new server degree permission. When granted, a login which include an auditor can view information in all databases that the consumer can connect to.

Question 26. What Is The New Security Features Added In Sql Server 2016?

Answer :

Always Encrypted:

This is a brand new feature that is beneficial for dealing with pretty touchy records
Unlike TDE it encrypts records at relaxation method bodily files (Data, Log and Backup), statistics in memory and facts in communication channels.
TEMPDB is uninvolved from encryption
Encryption may be applied to column stage.
A driving force that encrypts and decrypts the facts as it is despatched to the database server is hooked up on the purchaser.
Application connection string need to be modified.
Row Level Security:

This is first delivered in Azure SQL Database. Now it’s a part of on-premises characteristic from SQL Server 2016.
Data need no longer be encrypted but we can restriction the customers to look the touchy records. No master keys or certificates required as there's no encryption
Row-level security is based totally on a desk-valued feature which evaluates consumer get right of entry to to the desk primarily based on a security coverage this is applied to the desk.
The access tiers only applies to SELECT, UPDATE, DELETE operations but everybody who is having INSERT permissions can insert rows.
Only hassle with this is the use of consumer defined functions to manipulate user get admission to that's a massive drawback from overall performance prospect.
Dynamic Data Masking:

Masks data at pick out time based on consumer or database roles (Mainly for Read-most effective Users).
It actually doesn’t exchange the records however mask statistics based at the person who get entry to that data.
For instance:

I have a columns called “CredNo” to keep patron creditcard variety. If I masks this column then it'll be viewed as 22XXXXXXXXXX56.

But as I stated statistics isn't changed only this logic carried out and information is masked primarily based at the user/position.

A SYSADMIN or db_owner can view the real facts.

We can use four specific kinds of functions to mask data: Email, Partial, Default, Random