4 avg. rating (80% score) - 5879 votes
Are you prepared in attending an interview? Are you worried for job interview preparation? Then do not worry, we’ve a right answer for your job interview preparation. SQL Server offers a security architecture which is designed to allow database administrators and developers to create secure database applications and counter threats. If you are preparing for SQL Server Security job interview and don’t know how to crack interview and what level or difficulty of questions to be asked in job interviews then go through Wisdomjobs SQL Server Security interview questions and answers page to crack your job interview. SQL Server Security is an architecture providing developers and administrators to create and secure the database. Below are the list of frequently asked SQL Server Security interview questions and answers which can make you feel comfortable to face the interviews:
The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to any login, but can be used by any login. Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB
Any Windows login in BUILTINAdministrators group is by default a SQL Server system administrator. This single group can be used to manage administrators from a Windows and SQL Server perspective
Question 3. 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 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?
Do not give access to any other login on that database except for those 3 app logins.
Create a trigger that test each and every query like below
IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’)
To find out the orphan users:
To resolve the orphan user problem:
SysAdmin – Can perform any activity
ServerAdmin – Can change server configuration, restart, shutdown server
SecurityAdmin – Can manage server level logins, also can manage db level if they have permission on db
Granted: ALTER ANY LOGIN
ProcessAdmin – Can kill a process on an instance
Granted: ALTER ANY CONNECTION, ALTER SERVER STATE
DiskAdmin – Can manage the disk files
Granted: ALTER RESOURCES
BulkAdmin – Can perform BULK INSERT
Granted: ADMINISTER BULK OPERATIONS
SetupAdmin – Can add and remove linked servers
Granted: ALTER ANY LINKED SERVER
Dbcreator – Can create, alter, drop and restore any database on the instance
Granted: CREATE ANY DATABASE
Public – Default role for newly created login
sp_helpsrvrolemember : List out the members mapped with the server roles
Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role.
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 data collector
dc_proxy : Can administrate and use the data collector
PolicyAdministratorRole: can perform all configuration and maintenance activities on Policy-Based Management policies and conditions.
ServerGroupAdministratorRole : Can administrate the registered server group
ServerGroupReaderRole: Can view and the registered server group
dbm_monitor: Created in the msdb database when the first database is registered in Database Mirroring Monitor
Option1: Use the Dedicated Administrator Connection
Option2: Use BUILTINAdministrators Group
Option3: Change Registry Values
SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);
SELECT * FROM fn_my_permissions(‘AdventureWorks’, ‘DATABASE’);
SELECT * FROM fn_my_permissions(‘Employee’, ‘OBJECT’)
Ad-hoc remote queries
Custom solution with your application to log all logins into a centralized table
Date when the password was set
To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.
No. In SQL Server 2005 DDL triggers were introduced to audit CREATE, ALTER and DROP events for relational (stored procedures, functions, views, etc.) and security (certificates, logins, server, etc.) objects.
Any Windows login is by default a SQL Server system administrator, which may not be a desired situation
SQL Injection is an exploit where unhandledunexpected SQL commands are passed to SQL Server in a malicious manner. It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted.
If for some reason the resolution implemented does not resolve the problem and the SQL Injection attack occurs again, the quickest path may be to do the following:
By providing strong Passwords, Limited the access to make sure right people have access to the right data, Creating Customized database roles, server roles and assign privileges and by choosing the correct authentication mode etc.
A DBA should be careful in providing security…..General precautions includes:
We have plenty of features in SQL SERVER to enforce the security. The major features include:
In addition to these features we have some more added in SQL SERVER 2008, like Policy Based Management, Security Audit, Improved Encryption, Backup Security etc.
When we talk about the security we have to consider the below:
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?
SQL Server provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allow the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.
Application roles are database level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we provide a password to unlock access to the database.
Here it is how it works:
Default Schema for Windows Group Logins: Let’s say we have a Windows account [MyDomain WinAdmin]. If someone from this group logged in [MyDomainUser1] and tried to create an object then there will be a new schema created like [MyDomainUser1].Table. This issue got fixed in 2012. In 2012 we can assign a default schema for the Windows Group accounts.
User Defined Server Roles: Till 2008 R2 we have user defined roles at database level, 2012 allows us to create a Server level user defined roles which gives us more control in handling security.
Contained Database: Easier database migrations as it contains user and login information on same database instead of in Master.
Data Protection: Supporting Hash Algorithm-256 (SHA-256) and SHA-512.
Auditing: Native support/feature for auditing the database environment by creating the Audit specifications. We can also create user defined audits.
Ex: We can create an Audit specification to trace all events for a specific login and write all these event details into Audit Log. We can also filter the events.
Functionality Enhancement for TDE: In 2014 Transparent Data Encryption takes the normal backup and then applies the Encryption before writing it to the disk. It allows backup compression is useful when TDE enabled. TDE applies on compressed backup.
CONNECT ANY DATABASE: This is a new server level permission which can allow a login to connect all existing and future databases in the instance. This can be helpful when we need to give permissions for audit purpose.
IMPERSONATE ANY LOGIN: This is a new server level permission which gives us more control in giving/denying impersonate access to logins.
SELECT ALL USER SECURABLES: A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.
Row Level Security:
Dynamic Data Masking:
I have a columns called “CredNo” to store customer creditcard number. If I mask this column then it will be viewed as 22XXXXXXXXXX56.
But as I said data is not modified only this logic applied and data is masked based on the user/role.
A SYSADMIN or db_owner can view the actual data.
We can use 4 different types of functions to mask data: Email, Partial, Default, Random
SQL Server Security Related Tutorials
|Network Security Tutorial||Internet Security Tutorial|
|Security Testing Tutorial|
SQL Server Security Related Interview Questions
|Network Security Interview Questions||Internet Security Interview Questions|
|Security Testing Interview Questions||Oracle Security Interview Questions|
|Complex SQL Queries Interview Questions||Common SQL Queries Interview Questions|
|Sql Server Dba Interview Questions||Sql Loader Interview Questions|
|SQL Server Architect Interview Questions|
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.