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 Serverlevel 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##

 

 

 

Advertisements

3 thoughts on “SQL Server Execution Context and Security

  1. Alex

    Hello All

    Looking at you great post I would like to suggest you to look at GreenSQL database firewall for MSSQL. When connection is established to the database, GreenSQL is able to identify user, ip, sid, db name, schema. After that you have the firewall engine where yo can create rules based on that information.
    SQL Server Security

    Reply
  2. Liew

    I’m migrating the company database from MSSQL 2005 to MSSQL 2008 R2, I found the SQL 2005 contains the ##MS_AgentSigningCertificate##,##MS_SQLAuthenticatorCertificate##,##MS_SQLReplicationSigningCertificate## and ##MS_SQLResourceSigningCertificate##. The login should move to MSSQL 2008 R2 database and its compatible?

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s