Interview Questions.

Q1. What Is Sql Injection And Why Is It A Problem?

SQL Injection is an exploit where unhandledunexpected SQL commands are passed to SQL Server in a malicious way.  It is a problem because unknowingly information can be stolen, deleted, updated, inserted or corrupted.

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

Any Windows login in BUILTINAdministrators organization is by means of default a SQL Server device administrator. This single institution can be used to manipulate directors from a Windows and SQL Server perspective

Q3. How To Resolve The Orphan Use Problem?

To find out the orphan users:

USE <database_name>;

GO;

sp_change_users_login @Action='Report';

GO;

To resolve the orphan consumer hassle:

10USE <database_name>;

GO

sp_change_users_login @Action='update_one', 

@UserNamePattern='<database_user>',

@LoginName='<login_name>';

GO

Q4. What Are The Fixed Server Level Roles?

SysAdmin – Can perform any hobby

ServerAdmin – Can trade server configuration, restart, shutdown server

SecurityAdmin – Can manipulate server stage logins, can also manipulate db level if they have permission on db

Granted: ALTER ANY LOGIN

ProcessAdmin – Can kill a procedure on an example

Granted: ALTER ANY CONNECTION, ALTER SERVER STATE

DiskAdmin – Can manipulate the disk documents

Granted: ALTER RESOURCES

BulkAdmin – Can perform BULK INSERT

Granted: ADMINISTER BULK OPERATIONS

SetupAdmin – Can add and cast off related servers

Granted: ALTER ANY LINKED SERVER

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

Granted: CREATE ANY DATABASE

Public – Default position for newly created login

sp_helpsrvrolemember : List out the participants mapped with the server roles

Q5. Is Profiler The Only Tool That Has The Ability To Audit And Identify Ddl Events?

No. In SQL Server 2005 DDL triggers had been delivered to audit CREATE, ALTER and DROP activities for relational (stored methods, features, perspectives, and so forth.) and safety (certificate, logins, server, etc.) gadgets.

Q6. Name Three Of The Features Managed By The Surface Area Configuration Tool?

Ad-hoc far flung queries

Common language runtime

Dedicated Administrator Connection

Database Mail

Native XML Web Services

OLE Automation

Service Broker

SQL Mail

Web Assistant

xp_cmdshell

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

Always Encrypted:

This is a new feature which is useful for dealing with pretty sensitive facts

Unlike TDE it encrypts information at rest method physical documents (Data, Log and Backup), information in reminiscence and statistics in communique 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 sent to the database server is installed on the customer.

Application connection string need to be modified.

Row Level Security:

This is first added in Azure SQL Database. Now it’s part of on-premises feature from SQL Server 201@

Data want no longer be encrypted however we can limit the customers to see the touchy data. No master keys or certificates required as there is no encryption

Row-level security is primarily based on a desk-valued feature which evaluates user get right of entry to to the table based totally on a protection coverage this is carried out to the table.

The get entry to stages only applies to SELECT, UPDATE, DELETE operations but every person who's having INSERT permissions can insert rows.

Only hassle with this is the usage of consumer defined features to govern user get entry to that is a huge drawback from overall performance prospect.

Dynamic Data Masking:

Masks information at pick out time primarily based on consumer or database roles (Mainly for Read-handiest Users).

It simply doesn’t change the facts but mask data based at the person who get entry to that facts.

For example:

I even have a columns known as “CredNo” to store customer creditcard wide variety. If I mask this column then it'll be viewed as 22XXXXXXXXXX56.

But as I said information is not modified best this common sense implemented and facts is masked primarily based at the person/function.

A SYSADMIN or db_owner can view the actual data.

We can use 4 one-of-a-kind varieties of features to mask records: Email, Partial, Default, Random

Q8. We Have A List Of 3 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

Do now not give get right of entry to to some other login on that database besides for those 3 app logins.

Create a cause that check every and every query like beneath

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

raiserror (…..)

Return

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

Pros:

Any Windows login is via default a SQL Server gadget administrator

This single organization can be used to control SQL Server from a system administrators angle

Cons:

Any Windows login is via default a SQL Server gadget administrator, which might not be a desired state of affairs

Q10. What Are The Extra Roles Available In Msdb?

Db_ssisadmin: Equals to sysadmin

db_ssisoperator: Import/Delete/Change Role of personal 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 information collector

dc_proxy : Can administrate and use the records collector

PolicyAdministratorRole: can carry out all configuration and protection sports on Policy-Based Management policies and conditions.

ServerGroupAdministratorRole : Can administrate the registered server group

ServerGroupReaderRole: Can view and the registered server organization

dbm_monitor: Created in the msdb database when the first database is registered in Database Mirroring Monitor

Q11. What Are The Database Roles?

Db_accessadmin – Granted: ALTER ANY USER, CREATE SCHEMA, Granted with Grant alternative – 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 alternative: CONTROL

db_securityadmin – Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION

dbm_monitor – Granted: VIEW maximum current fame in Database Mirroring Monitor

sp_helprolemember : List out the members mapped with the server roles

Note:

Fixed database roles aren't equal to their database-level permission. For instance, the db_owner constant database function has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does now not make a person a member of the db_owner fixed database role.

Q12. What Options Are Available To Audit Login Activity?

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

Enable login auditing at the instance degree in Management Studio

Execute Profiler to seize logins into the instance

Leverage a 3rd party product

Q13. How To Recover From Sql Injection?

If for a few cause the decision applied does now not remedy the problem and the SQL Injection attack happens once more, the fastest course can be to do the following:

Shut down the internet web sites

Review the IIS logs to determine the commands issued and which internet pagecommand has the vulnerability

Convert the code to decide which tables were affected and the command issued

Find and update the string in your tables

Correct the net pagecommand that has the vulnerability

Test to validate the difficulty not occurs

Deploy the web pagecommand

Re-allow the web web sites

Q14. How Can Sql Server Instances Be Hidden?

To disguise a SQL Server example, we need to make a exchange in SQL Server Configuration Manager. To try this release SQL Server Configuration Manager and do the following: choose the example of SQL Server, right click on and select Properties. After deciding on homes you'll just set Hide Instance to “Yes” and click on OK or Apply. After the change is made, you need to restart the example of SQL Server to no longer expose the name of the example.

Q15. 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

SQLAgentUserRole:

SQL Server affords 3 fixed roles for the agent provider that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (no longer multiserver) that allow the member to work with their owned jobs (edit, start, stop, view records) without deleting the records of any activity.

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

The Guest person account is created via default in all databases and is used when express permissions aren't granted to get right of entry to an object.  It isn't always mapped at once to any login, however can be used by any login.  Depending on your safety wishes, it may make feel to drop the Guest consumer account, in all databases besides Master and TempDB

Q17. What Objects Does The Fn_my_permissions Function Reports On?

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’)

Q18. What Are The Security Related Catalog Views?

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

Q19. How To Enforce Security In Sql Server?

By providing robust Passwords, Limited the get entry to to ensure proper humans have get admission to to the right statistics, Creating Customized database roles, server roles and assign privileges and with the aid of deciding on the correct authentication mode etc.

A DBA have to be careful in offering safety…..General precautions includes:

Minimize the wide variety of sysadmins allowed to get admission to SQL Server.

Give users the least amount of permissions they need to perform their job.

Use stored methods or views to allow users to access records as opposed to allowing them to without delay get right of entry to tables.

When viable, use Windows Authentication logins instead of SQL Server logins.

Don’t provide permissions to the general public database position.

Remove user login IDs who no longer want 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 at the back of a firewall and are not exposed immediately to the Internet.

Using server, database and application roles to govern access to the statistics

Securing the bodily database files using NTFS permissions

Using an un guessable SA password

Restricting bodily get admission to to the SQL Server

Disabling the Guest account

Isolating SQL Server from the web server

Choose either of the provider to run SQL Server (Local User – Not an Admin , Domain User – Not an Admin)

Restrict the faraway 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 with the aid of installing a database server certificates.

Do no longer use DBO customers as application logins

Firewall regulations make sure that best the SQL Server listening port is available on the database server.

Remove the SQL guest consumer account.

Remove the BUILTINAdministrators server login.

Apply the state-of-the-art security updates / patches

We have masses of features in SQL SERVER to implement the safety. The essential functions include:

Password regulations

Encryption

Limited metadata visibility (machine Tables to Catalog Views)

DDL triggers

User-schema separation

Impersonation

Granular permission units

Security catalog views

In addition to these functions we've some greater brought in SQL SERVER 2008, like Policy Based Management, Security Audit, Improved Encryption, Backup Security and so on.

When we talk about the security we ought to remember the below:

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

Q20. What Are The New Security Features Added In Sql Server 2012?

Default Schema for Windows Group Logins: Let’s say we have a Windows account [MyDomain WinAdmin]. If a person from this organization logged in [MyDomainUser1] and tried to create an item then there will be a brand new schema created like [MyDomainUser1].Table. This problem were given constant in 201@In 2012 we will assign a default schema for the Windows Group accounts.

User Defined Server Roles: Till 2008 R2 we've got consumer defined roles at database level, 2012 permits us to create a Server stage consumer described roles which gives us extra manage in managing security.

Contained Database: Easier database migrations as it contains person and login information on equal database in preference to in Master.

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

Auditing: Native guide/characteristic for auditing the database surroundings via growing the Audit specs. We can also create person defined audits.

Ex: We can create an Audit specification to hint all activities for a specific login and write these types of event information into Audit Log. We also can filter the occasions.

Q21. What Is Application Role In Sql Server Database Security?

Application roles are database degree roles like database roles. We can create them and assign permissions to them much like regular database roles but we can’t map users with them. Instead, we offer a password to free up get entry to to the database.

Here it's far the way it works:

Create a login on SQL Server for application consumer

Create an software role on the corresponding database.

Give the utility role password to the user

User can have get right of entry to to login to SQL Server but doesn’t have any get entry to to the database which include public function.

He/she simply need to offer the password to unencumber the get admission to to the database

EXEC sp_addapprole ‘App_Role_Name’, ‘Password’

Once it's miles finished efficiently the user gets all rights that your app position have on that database.

Q22. How Can Sql Injection Be Stopped?

DevelopmentDBA:

Validate the SQL instructions which are being passed by using the the front quit

Validate the duration and statistics type in line with parameter

Convert dynamic SQL to saved procedures with parameters

Prevent any instructions from executing with the combination of or all the following instructions: semi-colon, EXEC, CAST,SET,  dashes, apostrophe, and so forth.

Based in your the front give up programming language decide what unique characters need to be eliminated earlier than any instructions are exceeded to SQL Server

Network Administration

Prevent visitors from unique IP addresses or domains

Review the firewall settings to decide if SQL Injection attacks can avoided

Remove vintage web pages and directories which might be no longer in use because these can be crawled and exploited

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

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTINAdministrators Group

Option3: Change Registry Values

Q24. Name 3 Of The Features That The Sql Server Built-in Function Loginproperty Performs On Standard Logins?

Date while the password was set

Locked out preferred login

Expired password

Must exchange password at subsequent login

Count of consecutive failed login attempts

Time of the last failed login attempt

Amount of time for the reason that password policy has been implemented to the login

Date while the login turned into locked out

Password hash

Q25. What Is The New Security Features Added In Sql Server 2014?

Functionality Enhancement for TDE: In 2014 Transparent Data Encryption takes the ordinary backup after which applies the Encryption before writing it to the disk. It permits backup compression is beneficial when TDE enabled. TDE applies on compressed backup.

CONNECT ANY DATABASE: This is a new server stage permission which can allow a login to connect all existing and future databases in the instance. This may be beneficial when we need to present permissions for audit motive.

IMPERSONATE ANY LOGIN: This is a new server degree permission which offers us more manage in giving/denying impersonate get right of entry to to logins.

SELECT ALL USER SECURABLES: A new server level permission. When granted, a login inclusive of an auditor can view data in all databases that the user can connect with.

Q26. How To Perform Backup For Certificates In Sql Server?

Using Native Backup

Using Backup Certificate Command