Browse
Tools
Categories

Assigning Roles for SQL Authenticated Users

Reference Number: AA-00828 Views: 7991 Last Updated: 06-17-2020 05:16 PM 0 Rating/ Voters

SiteAudit functional security allows users to be placed into roles that limit what the user is able to do within SiteAudit. This article explains how to create SQL authenticated users and assign them to roles within a SiteAudit database, thereby restricting what functions the user can perform. For an overview of the various roles,refer to SiteAudit Functional Security Roles.

 

There are two primary tools that can be used to create SQL users and assign them roles within a SiteAudit database. The SiteAudit Viewer and Microsoft SQL Server Management Studio. This article describes focuses on using the SiteAudit viewer to perform this function. To learn how to do this from within Microsoft SQL Server Management Studio, refer to the article Assigning Roles for Windows Authenticated Users as the procedure is the same.

 

Who Can Create SQL Users and Databases?

Creating SQL databases and users requires system administrator, sysadmin, rights on the SQL server. Enterprise environments typically have DBA's who administer the SQL server. If SQL Express is installed using the prerequisites downloaded from the Netaphor website, then the user who is logged into the machine and installs SQL Express is automatically assigned sysadmin rights. The system administrator has complete control of the SQL server and can create/delete databases and users.

 

Creating SQL Authenticated Users & Assigning Roles to a SiteAudit Database

The following is an overview of the actions required to create a SQL authenticated user and assign the user a role within a SiteAudit database. This procedure assumes that a SiteAudit database has already been created and the SiteAudit schema applied. The schema is automatically applied when the database is created from within SiteAudit.

  • Create a SQL user
  • Assign the user

 

Creating SQL Users

To create a SQL authenticated user, one must be a member of the sysadmin role on the SQL server. The purpose for creating SQL authenticated users is to allow users to access the SiteAudit database.

 

To create a new SQL user:

  • Open the Database Configuration dialog by selecting the Setup > Database menu
  • Ensure the correct server is specified



 

  • Click the Function Security button to open the Functional Security dialog
      


 

The Server Administration panel allows server administrators to create new SQL user accounts. If the credentials used to log onto the Server Administrator are not those of a sysadmin, then SQL user accounts cannot be created.

 

To create a new SQL user,

  • Type a new name into the User text box
  • Enter a password
  • Re-enter the password
  • Click the Save button

 

If the passwords match and are of sufficient complexity, the new user will appear in the SQL user accounts box. In the example below a new user, MaryAnn, has been created.



 

 

Providing Access to A SiteAudit Database

Once SQL users have been created, they can be assigned a role within a SiteAudit database. Place a user within a role restricts what that user is able to do within SiteAudit. For role details and permissions, refer to the SiteAudit Functional Security Role article.

 

It is possible for the same user to have a role within several SiteAudit databases but the user can be assigned only one role per database.

 

To assign a  SQL user permissions,

  • Select the SQL user account
  • Select the database to which the user will be given access
  • Click the Security menu and choose a role to grant the user

 

In this example MaryAnn is being assigned the Reviewer role to the 1_SiteAudit database



 

A dialog appears to confirm the action


 

When the Yes button is clicked, the user is assigned to the specified role within the selected database




 

MaryAnn now has Reviewer privileges to the 1_SiteAudit database. It is possible to assign multiple users to the Reviewers role or assign other users to the various other roles. A user can only be assigned to one role within a database.

 

 

Logging onto the SiteAudit Database using SQL authentication

Once a user is assigned a role within a database, he/she can log onto the database from the Database Configuration dialog.

 

In the previous example, MaryAnn was assigned the Reviewer role to the test_db.  MaryAnn's user name and password are entered into the Access credentials and the Database is then selected. Once the OK button is clicked, MaryAnn can perform all functions granted to a user in the Reviewer role.



 

Revoke Access to the SiteAudit Database

To revoke access to the SiteAudit database,

  • Select the SQL user
  • Select the SiteAudit database
  • Click the Access Level drop down menu and select NoAccess


 

In this example, MaryAnn will no longer have permission to access the test_db database.