Authorizing Users in SQL Server

< Previous section  |  Table of Contents  |  Index  |  Next section >

If you have checkmarked the option MainBoss manages SQL Security in the Defaults for Users section of Administration | Users, then MainBoss automatically gives new users appropriate permissions to access the MainBoss database through SQL Server. If you have turned off this option, you must authorize users manually. You might also have to authorize a user manually if someone else (or another program) has de-authorized the user for some reason.

In order to follow the steps described in this section, you must have SQL Server Administrator permissions on the SQL Server that manages MainBoss.

The following steps describe how to check whether a user has appropriate permissions for accessing the MainBoss database through SQL Server. The steps also describe what to do if permissions for a user have somehow been removed:

  1. On the computer where SQL Server is running, start SQL Server Management Studio.
  2. When Management Studio asks to specify the "Server Name", give the name of the instance of SQL Server that holds the MainBoss database. (This is the same server name you specified when you created the database.)
  3. Click Connect. Management Studio will open a window showing the configuration of the given instance of SQL Server.
  4. In the left-hand panel, expand the entry for Security.
  5. Under Security, click Logins. Management Studio will display a list of authorized users. This list may contain user groups as well as the login names of individual users.
  6. If the user you want to authorize isn't currently in the list:
Right-click Logins.
  • In the resulting menu, click New Login... Management Studio will open a window where you can authorize a new user.
  • In "Login name", enter the login name of the person you want to authorize. This must be a valid login name on the computer where SQL Server is running.
  • Click OK.
    1. Once the user's name is on the list of logins, you must check that the user has correct permissions for accessing the MainBoss database. Right-click the name of the user in the right-hand panel, then click Properties. MainBoss opens a window providing information about the user.
    2. In the left-hand panel of the "Login Properties" window, click User Mapping.
    3. In the right-hand panel, checkmark the entry for the MainBoss database (if it isn't already checkmarked). Management Studio will highlight the line and put the user's login name in the "User" column.
    4. The entry in the "Default schema" column should be "dbo". If it isn't:
    Click the drop-down button (...) at the end of this line. Management Studio opens a window where you can specify a default schema.
  • Under "Enter the object names to select", type "dbo".
  • Click OK. The original line should now give the name of the MainBoss database, the user's name, and "dbo".
    1. Under "Users mapped to this login", make sure the name of the MainBoss database is highlighted.
    2. Under "Database role membership for:", checkmark MainBoss (if it isn't already).
    3. In the left-hand part of the Login Properties window, click Status.
    4. Click Grant and click Enabled (if they aren't already).
    5. Click OK.
    6. Repeat Steps 6-15 for every user you want to authorize.

    Remember that the name you specify must be a valid login name for the current computer. When logging in from other computers, users must have the same name and password as on the computer where SQL Server is running. (This will always be true if you use domains, since the whole point of domains is to let users have the same name and password on multiple machines.)

    Spelling: If a particular user can't access the MainBoss database, always check that the user's name is spelled correctly in the various places it should appear, e.g. MainBoss's Users table and SQL Server's Logins list.

    Groups: SQL Server lets you grant permissions to user groups as well as individuals. Therefore, you might choose to use the above procedure to grant permissions to a "MainBoss User" group. Once you do that, you can add new users to the group and they automatically receive SQL Server permissions on the MainBoss database. However, the Users table inside MainBoss only allows individual login names, not groups.

    Note that you might be tempted to set up SQL Server permissions so that anyone can access the MainBoss database. However, MainBoss itself will only work for people explicitly authorized in the MainBoss Users table. If you grant SQL Server permissions to all users, you end up with the undesirable situation where some people may be prevented from using MainBoss itself, but could still change the database by hand (e.g. with Microsoft Access) if they wanted to. If someone can access the database, it's best to make sure that they do it by using MainBoss, not some other piece of software.

    Deleting Users: When you add a user to Administration | Users, MainBoss grants that user permission to connect with SQL Server (if you've turned on the MainBoss manages SQL Security option). However, when you delete a user from the Users table, MainBoss does not delete the user's permission to connect with SQL Server—the user may need "Connect SQL" permission in order to use SQL Server with another software package.

    If you wish to completely remove a user's access to SQL Server, remove the user's name from SQL Server's Logins list, using SQL Server Management Studio.

    < Previous section  |  Table of Contents  |  Index  |  Next section >