Sql-server – administrator permissions on a SSAS database

permissionssql serverssasssas-2016

I had a third party company coming over and building ssas databases to produce reports.

They did, but now the DBA wants to have a look but I haven't got permissions, to even see those databases, as you can see on the picture below.

Can I grant myself administrator permissions?

I am system administrator. there is a AD group called SQLDBA and

When right-cliking on the server-properties-security-Add-
then adding my AD group SQLDBA it says I don't have the permissions to alter the server object as you can see on the picture below.

I had previously added that sqldba AD group as an administrator to that box.

TITLE: Microsoft SQL Server Management Studio

Either the 'mycompany\MMiorelli' user does not have permission to alter the 'myserver\instance' object, or the object does not exist.
(Microsoft.AnalysisServices.AppLocal.Core)

BUTTONS:

Best Answer

The SSAS permissions are completely independent from SQL Server permissions.

If you don't have permissions, there is one hope:

By default, members of the local Administrators group are also granted administrative rights in Analysis Server. Although the local group is not explicitly granted membership in the Analysis Services server administrator role, local administrators can create databases, add users and permissions, and perform any other task allowed to system administrators. The implicit granting of administrator permissions is configurable. It is determined by the BuiltinAdminsAreServerAdmins server property, which is set to true by default.

Source: https://docs.microsoft.com/en-us/sql/analysis-services/instances/grant-server-admin-rights-to-an-analysis-services-instance?view=sql-analysis-services-2017

I suggest you to read the link above and try to run your Management Studio using an account that is member of Local Administrators group of your SSAS Server

Related Solutions

Sql-server – Find which permissions are being used for a given user on a object

Have a look at sys.user_token (http://technet.microsoft.com/en-us/library/ms188421.aspx) and sys.login_token (http://technet.microsoft.com/en-us/library/ms186740.aspx). They tell you which roles/logins/users SQL Server is involving when figuring out the permissions for the current user.

Then you can use sys.database_permissions (http://msdn.microsoft.com/en-us/library/ms188367.aspx) and sys.server_permissions (http://msdn.microsoft.com/en-us/library/ms186260.aspx) to get the permissions associated with each token. (They even tell you who granted the permission.)

Finally, sys.database_principals (http://msdn.microsoft.com/en-us/library/ms187328.aspx) and sys.server_principals (http://msdn.microsoft.com/en-us/library/ms188786.aspx) can be used to translate the principal_id into a name.

I took a stab at combining the system views above. It might not be perfect, but it should get you started:

SELECT  
        T.name token_name,
        T.type token_type,
        T.usage,
        T.is_login_token,
        UDP.name user_name,
        UDP.type_desc user_type_desc,
        UDP.default_schema_name,
        UDP.create_date,
        UDP.is_fixed_role,
        --UDP.authentication_type_desc,
        SP.name login_name,
        SP.type_desc login_type,
        SP.create_date,
        --SP.is_fixed_role,
        DP.class_desc,
        DP.permission_name,
        DP.state_desc,
        QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id))+'.'+QUOTENAME(O.name) object_name,
        O.type_desc object_type_desc,
        C.name column_name
  FROM (SELECT 0 is_login_token,* FROM sys.user_token 
        UNION ALL
        SELECT 1 is_login_token,* FROM sys.login_token
       )AS T
  LEFT JOIN sys.database_principals AS UDP
    ON T.principal_id = UDP.principal_id
   AND T.is_login_token = 0
  LEFT JOIN sys.server_principals AS SP
    ON (UDP.sid = SP.sid AND T.is_login_token = 0)
    OR (T.principal_id = SP.principal_id AND T.is_login_token = 1)
  LEFT JOIN sys.database_permissions AS DP
    ON (DP.grantee_principal_id = UDP.principal_id
        AND T.is_login_token = 0)
    OR (DP.grantee_principal_id = SP.principal_id
        AND T.is_login_token = 1)
  LEFT JOIN sys.all_objects AS O
    ON DP.major_id = O.object_id
  LEFT JOIN sys.all_columns AS C
    ON DP.major_id = C.object_id
   AND DP.minor_id = C.column_id;

Sql-server – Linked Server from SQL Server to SSAS not working – what is missing

I found this article here.

There it states that:

To resolve that problem you have 3 options:

Run SQL queries from data server (you need to be remotely connected to the database server)
Enable use of Kerberos on the database server
Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that is issuing t-sql query:

Create the linked server:

EXEC master.dbo.sp_addlinkedserver
@server = N'SSASSERVER', -- name of linked server
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP', -- see list of providers available on SQL Server under Linked Server node in SSMS Object Browser
@datasrc=N'ServerName', -- machine or instance name  that host Analysis Services
@catalog=N'SimplifiedCube' -- Analysis Services database (cube)

and adding a linked server login:

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'SSASSERVER',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'myDomain\Login',
@rmtpassword='########'

I have basically just done the number 3 and the linked server is working fine for me now. As you can see on the picture below.

enter image description here

Also I had problems with the timing out so I had to change this setting as well: (the default is 600)

EXEC sys.sp_configure N'remote query timeout (s)', N'6000'
GO
RECONFIGURE WITH OVERRIDE
GO

This can also be done through the GUI as per the picture below: