Security System of the Database Engine

The Database Engine’s security system includes two different security subsystems: Windows security SQL Server security Windows security specifies security at the operating system level—that is, the method by which users connect to Windows using their Windows user accounts. (Authentication using this subsystem is also called Windows authentication.) SQL Server security specifies the additional security necessary… [Continue Reading]

Encryption is a process of obfuscating data, thereby enhancing the data security. Generally, the concrete encryption procedure is carried out using an algorithm. The most important algorithm for encryption is called RSA. (It is an acronym for Rivers, Shamir, and Adelman, the last names of the three men who invented it.) The Database Engine secures… [Continue Reading]

The security of the Database Engine can be set up using SQL Server Management Studio T-SQL statements The following subsections discuss these two alternatives. Managing Security Using Management Studio To create a new login using SQL Server Management Studio, expand the server, expand Security, right-click Logins, and click New Login. The Login dialog box (see… [Continue Reading]

The Database Engine uses schemas in its security model to simplify the relationship between users and objects, and thus schemas have a very big impact on how you interact with the Database Engine. This section describes the role of schemas in Database Engine security. The first subsection describes the relationship between schemas and users; the… [Continue Reading]

There are three Transact-SQL schema-related statements: CREATE SCHEMA ALTER SCHEMA DROP SCHEMA The following subsections describe in detail these statements. CREATE SCHEMA Example 12.5 shows how schemas can be created and used to control database security. Note – Before you start Example 12.5, you have to create database users peter and mary. For this reason,… [Continue Reading]

A Windows user account or a SQL Server login allows a user to log in to the system. A user who subsequently wants to access a particular database of the system also needs a database user account to work with the database. Therefore, users must have a database user account for each database they want… [Continue Reading]

When several users need to perform similar activities in a particular database (and there is no corresponding Windows group), you can add a database role, which specifies a group of database users that can access the same objects of the database. Members of a database role can be any of the following: Windows groups and… [Continue Reading]

Application roles allow you to enforce security for a particular application. In other words, application roles allow the application itself to accept the responsibility of user authentication, instead of relying on the database system. For instance, if clerks in your company may change an employee’s data only using the existing application (and not Transact-SQL statements… [Continue Reading]

User-Defined Server Roles SQL Server 2012 introduces user-defined server roles. You can create and delete such roles using T-SQL statements CREATE SERVER ROLE and DROP SERVER ROLE, respectively. To add or delete members from a role, use the ALTER SERVER ROLE statement. Example 12.9 shows the use of the CREATE SERVER ROLE and ALTER SERVER… [Continue Reading]

Only authorized users are able to execute statements or perform operations on an entity. If an unauthorized user attempts to do either task, the execution of the Transact-SQL statement or the operation on the database object will be rejected. There are three Transact-SQL statements related to authorization: GRANT DENY REVOKE Before you read about these… [Continue Reading]