Security: Introducing Database Users and Roles

Securables are the entities that security principals are allowed or disallowed to access based on the permissions assigned to principals. Some securables contain other securables, which create nested hierarchies referred to as scopes. In SQL Server the three (3) main scopes are server, database and schema. In this post the securables that I will be focusing on are Users and Roles from the database scope. For a list of the securables in each scope visit MSDN: Secuarbles.

In the last blog I highlighted security principals and how to create server logins, however logins cannot access specific databases unless they are given appropriate database access or they are members of the sysadmin server role. This database access is achieved by creating a database user for each login that needs access to the database. This user creation and mapping can be done using SQL Server Management Studio (SSMS) or the following syntax:

     CREATE USER [Username] FOR LOGIN [login_name]

The [Username] does not have to be the same as the [login_name] but the [login_name] has to be a valid SQL server login. If a [login_name] isn't specified then SQL Server will try to create a user mapped to a login with the same name which means the user won’t be created if the [Username] is not the same as a valid SQL server login.
i.e.
     CREATE USER Tester FOR LOGIN Test - This syntax would work only if Test is an actual SQL server login.
     CREATE USER Tester - This syntax would work if Tester is an actual SQL server login because no [login_name] was specified.

Visit MSDN: Creating Database Users for more information about creating users via SSMS.

Just like how logins can become members of server-level roles to assist with management of logins is the same way users can become members of database roles to easily manage database users. Each database has a set of fixed database roles that can be used to group database users. The list of fixed database roles can be found on MSDN: Database-Level Roles. Unlike server-level roles, database roles can be created by users by using the syntax:

     CREATE ROLE [role_name]

Adding a user to a role can be done via SSMS by modifying the user's properties or the role's properties or by using the stored procedure sp_addrolemember.