Adding Accounts to Database Roles in SQL Server 2012

  • Article

The blog post below may be very simple to some admins who are familiar with SQL Server 2012, but recently I ran across an article about configuring SQL database replicas for Management Points in ConfigMgr 2012.  In that article, there was a step asking me to add a computer account to the db_datareader role in SQL.  It did not go into depth on how to do this, so I put the following post together to outline that process. 

While the post below will discuss adding user accounts to SQL, my main concern in writing this is to document how to get a computer account added.  It requires a few extra steps, but can definitely be done.

I hope it’s helpful.

Summary

When working with Configuration Manager, it is sometimes necessary to make changes to the security of the underlying SQL database. A specific example of this is when setting up a database replica on a Management Point. One of the steps in this process is outlined below:

  • For each management point that uses a database replica, you must manually add the computer account of the management point server to the db_datareader role for the database replica.

This statement can be found in the following document:

Configure Database Replicas for Management Points

When encountering instructions such as this, it is helpful to understand the underlying SQL processes necessary so that security can be correctly set up.

Background

In SQL Server 2012, there are three essential definitions that are important to understand before attempting the task listed above. These are:

  • SQL Logins – these are the credentials used to authenticate connections to a database instance. To access database resources, an object must be associated with a SQL Login
    • There are multiple types of logins, including Windows authentication, SQL Server authentication, Certificate and Asymmetric Key authentication. For the purposes of the present document, we will focus on Windows authentication.
  • Database Users – this is the SQL Server login used when connecting to the database. Users are associated with logins in order to grant the appropriate level of access to the desired database resources
  • Database Roles – similar to groups, roles are logical groupings of SQL permissions to which users can be assigned. When a user is assigned to a role, it is automatically granted all of the permissions that have been granted to that role. Roles exist at the database level, and may be either fixed (pre-existing roles that are created by default and cannot be altered) or flexible (user-created roles with custom sets of permissions)
    • NOTE: For the present document, we will focus on fixed database roles

When using Windows authentication, the underlying OS is responsible for ensuring that authentication happens. The following object types can be used when configuring SQL Logins using Windows authentication:

  • User accounts
  • Computer accounts
  • Security groups

Best practices when configuring access to resources dictates that a security group is configured and granted the necessary permissions and rights to the resource. Then users, computers and even other groups can be added to the first group as needed. In other cases, it may be necessary to add a user or computer object directly.

The principle of assigning a Windows account (group, user or computer) to a role involves the following steps:

  1. Create a SQL Login
  2. Associate the SQL Login with a Database User
  3. Add the Database User to the Database Role

For the present document, we will add a computer object directly. Though the process is largely the same, the steps of doing this are slightly more complicated than adding a user or group. Differences between these two processes will be obvious, but will be mentioned for clarity at the appropriate points.

Per the statement in the summary above, our interest is in adding the Management Point computer to the db_datareaders database role. The following steps outline in general how to accomplish this task. I have deliberately included steps that will work with users and groups, but not with computer objects for completeness.

Creating a SQL Login (users and groups)

The following steps work when the goal is to add a user or group object. It does not allow adding a computer object, as noted at the appropriate point below.

Before a Windows user can be added to the desired role, it must first have a SQL Login created. To do this, take the following steps:

  1. Open SQL Server Mgmt. Studio and connect to the database
  2. At the top level of object explorer beneath the SQL Server node, right-click ‘Security’ and choose New -> Login… as shown below

clip_image001

  1. In the Login – New dialog box, ensure that ‘Windows authentication’ is selected and choose ‘Search…’ to identify the user or group that will be associated with the SQL Login

clip_image002

  1. In the ‘Select User or Group’ dialog box, ensure the correct parameters are chosen and enter the name of the user or group that will be associated with the SQL Login

clip_image004

NOTE: If the goal is to use this process to select a computer account, you will receive the following message after typing in the computer name and selecting ‘Check Names’:

clip_image006

When selecting ‘Object Type’s to ensure that Computers are included in the results, it becomes obvious that this method cannot be used to associate a computer with a SQL Login.

  1. At this point, if I am trying to add a user or group that object these are all the steps that are needed. Merely type the user or group name into the ‘Select User or Group’ dialog box and choose ‘Check Names’. The name will resolve with the authenticating authority (Active Directory or the local SAM database) and you can choose ‘OK’.
  2. Select ‘OK’ again to close the Login – New dialog box and then expand Security\Logins to see that your chosen object has been added. In the screenshot below, I added the SQL Admins group that resides in the Test AD forest:

clip_image007

This process works well when adding a user or group. But if there is a need to add a computer, that must be accomplished a different way

Creating a SQL Login (computers)

The principle remains the same if we need to add a computer object to a database role in SQL. We must still create a SQL Login, associate that login with a database user, and place that database user into the desired database role. However, creating a SQL Login for a computer must be handled through a SQL statement directly.

The following SQL statement will allow adding a computer object as a SQL Login:

create login [test\na-pr1-ss$] from windows;

go

In my case, I am adding the computer object known as na-pr1-ss that belongs in the Active Directory forest known as ‘test’. Note three things about this SQL statement:

  1. The brackets around test\na-pr1-ss are not optional. They are needed for this statement to work correctly
  2. The $ at the end of the computer name is also not optional. If this is missing, the SQL statement will report that it cannot find the computer object
  3. The GO statement is needed to commit the change

Once the SQL statement has been run, if it’s successful you can verify it was successful with the following SQL query:

select * from sys.server_principals where name like '%na%'

It is also possible to expand Security\Logins as shown in the screenshot above. This also shows that the computer account has been added.

Associating the SQL Login with a Database User

From this point forward, the process is the same regardless of whether you are working with a user, group or computer. Everything is now a SQL Login and SQL Logins can be handled in a uniform manner.

To create a new Database User that is associated with your newly created SQL Login, take the following steps:

  1. Expand the Object Explorer in SQL Server Mgmt. Studio to Databases\<SQL Database>\Security\Users
  2. Right-click the Users node and choose New User… as shown below:

clip_image008

NOTE: This is not the same Security container worked with in the steps above. This container is specific to the SQL Database itself. SQL Logins are created at the top-level Security container. Users are created within the database-specific Security container.

  1. In the Database User – New dialog box, ensure that the User type is ‘SQL user with login’ and select the elipsis (…) next to Login name

clip_image010

  1. In the Select Login dialog box, enter either the partial or complete name of the SQL Login and select ‘Check Names’
    1. If desired, you can also select the ‘Browse’ button and you will see a complete list of SQL Logins that have previously been created.

clip_image012

  1. In the resulting dialog box, select the SQL Login desired and click ‘OK’

clip_image013

  1. Back in the Database User – New dialog box, enter a name for this database use in the User name field
    1. NOTE: This will become the display name for the database user, so it does not have to be identical with the SQL Login name (though it can be)
  2. Click OK to finish creating the new database user.
    1. NOTE: The default schema can be left blank. It does not have to be populated to create the database user

Add the Database User to a Database Role

At this point, we have a SQL Login that has been associated with a database user. Finally, we can take the final step and add the database user to the desired role. In our case, we are interested in the db_datareader role.

To see a list of the fixed roles within the SQL database, navigate to Databases\<SQL Database>\Security\Roles\Database Roles.

clip_image015

To add the database user to the desired database role (in this case db_datareader), take the following steps:

  1. Right-click db_datareader within the Database Roles container and choose Properties
  2. In the Database Role Properties – db_datareader, select Add and add the database user using the same process as above when adding a SQL Login to a database user.

clip_image017

  1. Choose OK after confirming that the Members of this role window now contains your chosen database user (NOTE: database roles can also be nested inside of each other using this same process)

Summary

The process of adding a objects to a database role in SQL is not very well documented. The majority of the documentation available assumes either a user or group will be used, and only a few references in social sites even raised the question of adding a computer account. This document’s purpose has been to clear up that mystery.

Comments

  • Anonymous
    January 16, 2015
    Very, very helpful piece of information. Thanks Scott!
  • Anonymous
    April 14, 2015
    Thank you
  • Anonymous
    July 03, 2015
    Very useful article....!!!
  • Anonymous
    September 04, 2015
    Thank you ver helpful. A problem we still have is at the sql login level. If we add ther the domain admins group and not the single users from this group we have the effect, that the admin can login but cannot see all content like the logins (only sql login like sa but no tohter domain user or groups)
    Scott maybe you also a solution for this case, would be very happy about.!
  • Anonymous
    September 09, 2015
    And I got the solution for one more problem....Thank you :-)
  • Anonymous
    April 13, 2016
    Extremely clear - worked first time, many thanks!