SQL Server User Permissions

[david.dagenette]

This article will provide an overview of SQL Server user permissions.

Permission Entities and Types

• Login: A set of credentials that can be used connect and authenticate with SQL Server.

  

  • Windows Authentication: Windows Authentication is used when logging in with the credentials of a local Windows user account or Active Directory domain account. No password is specified when adding these users since the password used comes from the local user or domain user.

  • SQL Server Authentication: SQL Server Authentication is used to create a set of credentials specific to the SQL Server instance. This requires a password to be specific for authentication.

  • Enforce password policy: This option is required if you want to enforce password expiration or require a password change at next login. Additionally, if this option is unchecked, this user will no longer be locked out after multiple login failures. If your SQL service is publicly accessible, it is recommended to leave this option checked so that brute-force login attacks are mitigated by user lockout.

  • Default Database: Every SQL connection/session is associated with a database. Every Login has a default database in-case no database is specified during authentication. If the Login does not have permissions to the Default Database, authentication will fail.

• Server Roles: A set of permissions that can be assigned to a login. These permissions are server-wide, affecting the login’s permissions on all databases and their permission to specific server-level operations.

  

  The below image provides a graphical representation of the permissions encompassed by each role. The sysadmin fixed server role encompasses the entire diagram since it is allowed all permissions while the other roles only allow specific permissions. Additionally, since the securityadmin role allows the user to add additional permissions to themselves, it should be seen as equivalent to giving a user full system access to the sysadmin role.

  
  • On our shared servers, we revoke the VIEW ANY DATABASE permission from the public fixed server role to prevent users from being able to see other users’ databases. With this change in-place, users can only see databases that are owned by their Login.

• Databases: A user’s permission to a database is controlled in 2 ways:

  • Database Ownership: The database itself has a single owner that can be assigned on the Files tab of the database properties page.

    

    The Login assigned as the owner here has full permissions to the database. This Login is also able to see the database within SQL Server Management Studio, even when the VIEW ANY DATABASE permissions have been revoked from the Public fixed server role.

  • Database User Mapping: A Login may be mapped to a Database User, which gives the log in the permissions in the database that have been assigned to that user. More on Database Users is below.

• Database Users: A Database User is an entity that can be assigned a specific set of permissions within a database and mapped to a SQL Login. The User can be assigned permissions in 3 ways:

  • Database Role Assignment: You can assign the database user one or more Database Roles, which will give the user the permissions allowed by those roles.

  • Database Schema Ownership: You can make the Database User the owner of one or more Database Schemas, which will give the user permissions to any database objects (Tables, Stored Procedures, Functions, etc.) owned by that Schema.

  • Explicit Permissions Assignment: You can explicitly assign permissions for the user to any object within the database. This allows extremely granular permissions, such as SELECT permission to a specific Database Table or Execute permission to a specific _Stored Procedure.

• Database Schemas: A Database Schema is a container object for database objects. All database objects are owned by a Database Schema. The default schema is the DBO schema. You can reference objects owned by the DBO schema without qualifying the name of the object with the Schema name. For objects owned by any other Database Schema, you must qualify the object name with the schema name, unless it is configured as the Default Schema for that user.

  For example, given a table named [Accounts] that is owned by a Schema named [MySchema], you would have to reference the table as [MySchema].[Accounts] unless MySchema was configured as the Default Schema for your user.

  

  For the average user, there is not a need to use any Schema other than the built-in DBO Schema. For more complex databases, Schemas can be used to organize objects into groups. For example, if two different applications are sharing the same database, each can group its own application-specific tables underneath its own Schema to prevent name conflicts and simplify permissions for each application.

• Database Role: A Database Role is a container of one or more permissions that can be assigned to one or more users. Database Roles can also own Schemas, which can allow multiple Users to have effective ownership of the same Schema by both being assigned to the Database Role that owns the _Schema.

  Below is an image showing the different roles and their permissions. The db_owner role encompasses the entire diagram because it grants all permissions on the database.

  

Ownership Hierarchy

This is a tree diagram of the hierarchy of ownership between SQL Server entities:

• Login
  • Database
    • Role
      • Schema
        • Database Object
    • User
      • Schema
        • Database Object

Fully Qualified Object Name Format:
[SERVER].[DATABASE].[SCHEMA].[OBJECT]

Permission Hierarchy

This is a tree diagram of the hierarchy of permission assignment:

• Login
  • Server Role Assignment
  • Explicit Server Permission Assignment
  • Database Ownership
  • User Mapping
    • Database Role Assignment
      • Schema Ownership
        • Explicit Database Permission Assigment
    • Schema Ownership
      • Explicit Database Permission Assigment
    • Explicit Database Permission Assigment

Additional Considerations

• Each Login has an SID (unique identifier). When a Login is mapped to a specific Database User, that user is mapped to the Login’s SID, not the Login’s name.

  • You can fix a mismatch between the SID assigned to Database User and the SID of Login using the following sp_change_users_login stored procedure. Example:

    EXEC sp_change_users_login 'auto_fix', 'USERNAME';

• When a Login is configured as the owner of a Database, it is the Login name that is assigned as the database owner, not the Login’s SID.