Introduction
In the third part of our setup guide, we will walk you through setting application database permissions for your SSAS SI. This procedure applies whenever you add new application databases to your environment. You have the option to set permissions manually or with a SQL script.
Note: You do not need to configure these permissions if your SSAS SI is a system admin. We, however, strongly recommend against this for production environments.
Before you begin
Please complete the following before continuing with this article:
- Configured your SSAS SI login in SQL Server. Learn more.
- Your SSAS SI has the required server-level permissions. Learn more.
- Your target application database has been restored to SQL Server. Learn more.
Set permissions manually
1. Open SQL Server Management Studio (SSMS) and connect to your SQL server.
2. Expand your server > Security > Logins.
3. Right-click your SSAS SI login and select Properties.
4. Under Select a page, go to User Mapping.
5. For the application database, select its Map checkbox and then the db_datareader mappings.
6. In the Default Schema column, select ... for the application database.
7. Enter dbo as the object name and select OK.
8. Repeat steps 5-7 for any additional application databases.
9. Select OK to finalize changes.
Set permissions with SQL script
1. Open SQL Server Management Studio (SSMS) and connect to your SQL server.
2. Select New Query.
3. Copy/paste the following script into the query.
USE [<KEPION_APPLICATION_DATABASE>]
GO
IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'U' AND [name] = N'<SSAS_SI>')
BEGIN
CREATE USER [<SSAS_SI>] FOR LOGIN [<SSAS_SI>] WITH DEFAULT_SCHEMA = [dbo];
END;
ALTER USER [<SSAS_SI>] WITH DEFAULT_SCHEMA = [dbo];
EXEC sp_addrolemember N'db_datareader', N'<SSAS_SI>'
Note: Replace the <placeholders> with your information.
4. Select Execute to run the query.