Certain SQL Server permissions are necessary for AYS users to access the data and run reports. This article will assist you in configuring the security on Microsoft SQL Server 2012 R2.
Note: At Your Service Software, Inc. will not provide support for installing or administering SQL Server on your hardware and network. Please consult a local IT service provider and/or Microsoft for this service.
Step 1. Create a new login
Launch SQL Server Management Studio and create a new login. The screenshots to follow will use SQL Server authentication for illustrative purposes, you may use either Windows Authentication or SQL Server authentication.
Step 2. Grant rights to create databases to the new user
The new user will need rights to create databases so that AYS can construct the correct database structure. Select dbcreator from the “Server Roles”
Step 3. Grant “View Server State” rights to the new user
AYS concurrent user licensing requires each user have “View Server State” rights to function correctly. First, search the server objects and add the server in the securables section.
Once it has been added, select the server in the list and grant “View server state” at the bottom section of the same window.
Step 4. Create the AYS database
Open AYS and select File – New Database – Empty Microsoft SQL Server Database… enter your SQL Server login information. User ID and password will be obscured with asterisks.
Click Login and choose a database name. Note that the database will be created on the server with a prefix of AYS_, so the database named “yourdatabase” can be found on the server as “AYS_yourdatabase”. When you click “Create” the database should be created on the server.
5. Grant basic database rights to all the users
The user ID used to create the database will be the database owner (dbo) and will have all the rights necessary on the SQL database. For other users, you will need to go into their login properties and choose “User Mapping”, select the database and choose “db_datareader” and “db_datawriter“. You will also need to grant the “View server state” permission as described in step 3 above.
Repeat this step for every user you are granting permissions on the server. If you are familiar with SQL Server, you can create a server role with the necessary permissions and add users to this role. A detailed description of this is beyond the scope of this document, please see your local IT consultant for instructions on how to do this.
See Also
Is AYS compatible with Microsoft SQL Server 7?
Is AYS compatible with Microsoft SQL Server 2000?
Is AYS compatible with Microsoft SQL Server 2005?
Is AYS compatible with Microsoft SQL Server 2008?
Is AYS compatible with Microsoft SQL Server 2008 R2?
Is AYS compatible with Microsoft SQL Server 2012?
Is AYS compatible with Microsoft SQL Server 2014?
1 person found this article useful
1 person found this article useful