SQL Server Execution Context and Security

What is Execution Context?

Whenever User log on or connects to SQL Server, it will create the User Session. Whenever that user executes the statement SQL Server uses that session’s user id, permission, password to execute the query. That session is called execution context.

 

Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. A login connecting to an instance of SQL Server has one login token and one or more user tokens, depending on the number of databases to which the account has access.

 

What is Principals in SQL Server 2005?

Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope. Every principal has security identifier

 

-> Windows level

-> SQL Server level

-> Database level 

Windows-level principals

• Windows Domain Login


• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User


• Database Role


• Application Role

What are SQL Server Securables?

SQL Server Securables contains three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables. The securable scopes are as follows:

·         Server: It includes server roles, logins, etc.

·         Database: It includes database users, application roles, database roles, etc.

·         Schema: It includes various database objects such as tables, views, procedures, etc.

The securables are used to assign permissions to the users based on scope and the tasks assigned. The issues related to the connectivity to databases, accessing database objects, etc., can be resolved by granting or denying the permissions to the users.

 

What is the use of the Public database role in SQL Server?

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable. 

Explain Certificate based SQL Server Logins/Principals?

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

• ##MS_SQLResourceSigningCertificate##


• ##MS_SQLReplicationSigningCertificate##


• ##MS_SQLAuthenticatorCertificate##


• ##MS_AgentSigningCertificate##


• ##MS_PolicyEventProcessingLogin##


• ##MS_PolicySigningCertificate##


• ##MS_PolicyTsqlExecutionLogin##