The Database Engine’s security system includes two different security subsystems: Windows securitySQL Server security Windows security specifies security at the operating system level—that is, the method by which users connect to Windows using … [Read more...] about Authentication, Implementing an Authentication Mode
Security System of the Database Engine
Encrypting Data, Methods of Data Encryption
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, … [Read more...] about Encrypting Data, Methods of Data Encryption
Setting Up the Database Engine Security
The security of the Database Engine can be set up using SQL Server Management StudioT-SQL statements The following subsections discuss these two alternatives. Managing Security Using Management Studio To create a new login using SQL … [Read more...] about Setting Up the Database Engine Security
Schemas – User-Schema Separation
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 … [Read more...] about Schemas – User-Schema Separation
DDL Schema-Related Statements
There are three Transact-SQL schema-related statements: CREATE SCHEMAALTER SCHEMADROP SCHEMA The following subsections describe in detail these statements. CREATE SCHEMA Example 12.5 shows how schemas can be created and used to control … [Read more...] about DDL Schema-Related Statements
Database Security – Managing Using SSMS & T-SQL
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 … [Read more...] about Database Security – Managing Using SSMS & T-SQL
SQL Server Roles /Fixed Server Roles/Fixed Database Roles
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 … [Read more...] about SQL Server Roles /Fixed Server Roles/Fixed Database Roles
Application Roles – Roles in SQL Server
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, … [Read more...] about Application Roles – Roles in SQL Server
User-Defined Server Roles & Database Roles
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 … [Read more...] about User-Defined Server Roles & Database Roles
GRANT Statement – Authorization in SQL Server
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 … [Read more...] about GRANT Statement – Authorization in SQL Server
DENY Statement , REVOKE Statement
The DENY statement prevents users from performing actions. This means that the statement removes existing permissions from user accounts or prevents users from gaining permissions through their group/role membership that might be granted in the … [Read more...] about DENY Statement , REVOKE Statement
Managing Permissions Using Management Studio
Database users can perform activities that are granted to them. In this case, there is a corresponding entry in the sys.database_permissions catalog view (that is, the value of the state column is set to G for grant). A negative entry in the table … [Read more...] about Managing Permissions Using Management Studio
Managing Authorization and Authentication of Contained Databases
As you already know from Chapter "Data Definition Language", contained databases have no configuration dependencies on the server instance where they are created and can therefore be easily moved from one instance of the Database Engine to another … [Read more...] about Managing Authorization and Authentication of Contained Databases
Change Tracking
Change tracking refers to documenting all insert, update, and delete activities that are applied to tables of the database. These changes can then be viewed to find out who accessed the data and when they accessed it. There are two ways to do … [Read more...] about Change Tracking
Data Security and Views
As already stated in Chapter "Views", views can be used for the following purposes: To restrict the use of particular columns and/or rows of tablesTo hide the details of complicated queriesTo restrict inserted and updated values to certain … [Read more...] about Data Security and Views
