SQL SERVER SECURITY

What is the use of db_owner role?

Users in the db_owner role have it all, within a single database. They can grant and revoke access, create tables, stored procedures, views, run backups and schedule jobs. A user who is db_owner can even drop the database.

Why Use db_datareader role?

Able to create and run ad hoc reports directly against this database, but they shouldn't be able to do anything else apart from seeing the contents of the database.

Creating custom database application role, is bad practice or good practice and why?

It is good practice. Granting permissions to roles rather than to users simplifies security administration. Permission sets that are assigned to roles are inherited by all members of the role.

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

What is the difference between logins and users?

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.

What does Database Encryption and Decryption mean?

Database encryption is the process of converting data, within a database, in plain text format into a meaningless cipher text by means of a suitable algorithm. Database encryption can be provided at the file or column level.

What do we mean by Database Security?

Database security refers to the collective measures used to protect and secure a database or database management software from illegitimate use and malicious threats and attacks.

Is it possible to disable SA account, 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. ALTER LOGIN sa DISABLE;

What is the use of db_ddladmin role?

Members of the db_ddladmin role are able to execute DDL commands (CREATE, ALTER, DROP) within the current database. They cannot create new databases, nor can they alter or drop the current database.

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

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.

7) What are examples of securable a the Server and Database 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

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.

How do you carnage Authentic modes in SQL Server

TClick Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

What is the Guest user account in SQL Server? What login is it mapped to it?

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.

How many type of SQL Server authentication mode are available in SQL Server?

There are two type of authentication available in SQL Server are Windows mode and Mixed Mode - SQL and Windows.

As 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 • • 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. • 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. •

What is the difference between Authenticate and Authorization?

When you log on to a PC with a user name and password you are authenticating. Authorization is the process of verifying that you have access to something.

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.

Which authentication mode is more secure?

Windows authentication is definitely more secure as it's controlled and authenticated by Active Directory policies.

Is it possible to Rename the SA Login? How?

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];

is it possible to create new User Defined Server role?

Yes, it is possible to create a Server role in SQL Server

What are Fixed Database Roles?

db_owner Users who can perform almost all activities in the database db_accessadmin Users who can add or remove users db_datareader Users who can see data from all user tables in the database db_datawriter Users who can add, modify, or delete data in all user tables in the database db_ddladmin Users who can perform all DDL operations in the database db_securityadmin Users who can manage all activities concerning security permissions in the database db_backupoperator Users who can back up the database db_denydatareader Users who cannot see any data in the database db_denydatawriter Users who cannot change any data in the databa