Get all Database level permissions for a User on all Databases in MS SQL Server using T-SQL

The variable @UserName should be changed before running the query

IF OBJECT_ID('tempdb.dbo.#UserPermissions', 'U') IS NOT NULL DROP TABLE #UserPermissions; 

Declare rscursor cursor read_only
FOR
SELECT DatabaseName = name FROM [master].[sys].[databases];
Declare @DatabaseName nvarchar(500);
Declare @sql nvarchar(max)
Create table #UserPermissions(
DatabaseName nvarchar(max),
LoginName nvarchar(max),
LoginType nvarchar(max),
DatabaseUserName nvarchar(max),
[DatabaseRole] nvarchar(max),
PermissionType nvarchar(max),
PermissionState nvarchar(max),
ObjectType nvarchar(max),
ObjectName nvarchar(max),
ColumnName nvarchar(max))

OPEN rscursor
Fetch next from rscursor into @DatabaseName
Declare @UserName nvarchar(500) =  'domain\gina.alexa'; ----Change UserName here

While @@fetch_status=0
Begin
Set @sql=
'SELECT
@DatabaseName as ''DatabaseName'', 
    [LoginName] = ''All Users'',
    [LoginType] = ''All Users'', 
    [DatabaseUserName] = ''All Users'',       
    [Role] = p.[name] COLLATE Latin1_General_CI_AI,      
    [PermissionType] = pe.[permission_name],       
    [PermissionState] = pe.[state_desc],       
    [ObjectType] = ob.type_desc,  
    [ObjectName] = ob.[name],
    [ColumnName] = c.[name]
FROM ' + @DatabaseName +'.sys.database_principals p
LEFT JOIN ' + @DatabaseName +'.sys.database_permissions pe ON pe.[grantee_principal_id] = p.[principal_id]
LEFT JOIN ' + @DatabaseName +'.sys.columns c on c.[object_id] = pe.major_id AND c.[column_id] = pe.[minor_id]                   
INNER JOIN ' + @DatabaseName +'.sys.objects ob ON ob.[object_id] = pe.[major_id]
WHERE p.[type] = ''R''
AND p.[name] = ''public''
AND ob.is_ms_shipped = 0

------
UNION ALL
SELECT 
@DatabaseName as ''DatabaseName'', 
[LoginName] = l.[name] COLLATE Latin1_General_CI_AI,
    [LoginType] = p.[type_desc] COLLATE Latin1_General_CI_AI,  
    [DatabaseUserName] = p.[name] COLLATE Latin1_General_CI_AI,       
    [Role] = null,      
    [PermissionType] = pe.[permission_name],       
    [PermissionState] = pe.[state_desc],       
    [ObjectType] = pe.[class_desc],
    [ObjectName] = ob.[name], ----OBJECT_NAME(pe.major_id),
    [ColumnName] = c.[name]
FROM ' + @DatabaseName +'.sys.database_principals p 
LEFT JOIN ' + @DatabaseName +'.sys.server_principals l on p.[sid] = l.[sid]
LEFT JOIN ' + @DatabaseName +'.sys.database_permissions pe ON pe.[grantee_principal_id] = p.[principal_id]
LEFT JOIN ' + @DatabaseName +'.sys.columns c ON c.[object_id] = pe.major_id AND c.[column_id] = pe.[minor_id]
LEFT JOIN ' + @DatabaseName +'.sys.objects ob ON pe.[major_id] = ob.[object_id]
WHERE p.[name]= @UserName
---
UNION ALL
SELECT  
@DatabaseName as ''DatabaseName'', 
[LoginName] = l.[name] COLLATE Latin1_General_CI_AI,
    [LoginType] = mp.[type_desc],  
    [DatabaseUserName] = mp.[name],   
    [Role] = rp.[name],      
    [PermissionType] = pe.[permission_name],       
    [PermissionState] = pe.[state_desc],       
    [ObjectType] = ob.type_desc,
    [ObjectName] = ob.[name], ---OBJECT_NAME(perm.major_id),
    [ColumnName] = c.[name]
FROM ' + @DatabaseName +'.sys.database_role_members m
INNER JOIN ' + @DatabaseName +'.sys.database_principals rp ON rp.[principal_id] = m.[role_principal_id]
INNER JOIN ' + @DatabaseName +'.sys.database_principals mp ON mp.[principal_id] = m.[member_principal_id]
LEFT JOIN ' + @DatabaseName +'.sys.server_principals l on mp.[sid] = l.[sid]
LEFT JOIN ' + @DatabaseName +'.sys.database_permissions pe ON pe.[grantee_principal_id] = rp.[principal_id]
LEFT JOIN ' + @DatabaseName +'.sys.columns c on c.[object_id] = pe.major_id AND c.[column_id] = pe.[minor_id]
LEFT JOIN ' + @DatabaseName +'.sys.objects ob ON pe.[major_id] = ob.[object_id]
WHERE mp.[name]= @UserName'

Insert into #UserPermissions(
DatabaseName,
LoginName,
LoginType,
DatabaseUserName,
[DatabaseRole],
PermissionType,
PermissionState,
ObjectType,
ObjectName,
ColumnName) 
Execute sp_executesql @SQL, N'@DatabaseName nvarchar(max), @UserName nvarchar(500)', @DatabaseName = @DatabaseName, @UserName = @UserName;

FETCH NEXT FROM rscursor INTO @DatabaseName
End

CLOSE rscursor
DEALLOCATE rscursor
GO

Select * from #UserPermissions
Order by LoginName, DatabaseName;

OLE DB Provider in Excel Services on SharePoint 2013

Excel Services on Sharepoint 2013 - Data Connection failed to refresh

Error: We were unable to refresh one or more

data connections in this workbook.

The following connections failed to

refresh:

Problem:

PivotTable created in Excel 2013 with External Data Connection to SSAS 2012.

The report works fine and also the refresh. Below is a screenshot of the Connection Properties and notice the Connection string.

The same file, when uploaded on Sharepoint 2013, gives the error above on Refresh.

 

Reason: Connection String > Provider=MSOLAP.6

Normally, MSOLAP.5 is the Data Provider used in Excel 2013 for SSAS 2012.

Good to know:

"Analysis Services OLE DB Provider is the native provider for Analysis Services database connections. MSOLAP is used indirectly by both ADOMD.NET and AMO, delegating connection requests to the data provider. 

ADOMD.NET is a managed data provider used for querying Analysis Services data. Excel uses ADOMD.NET when connecting to a specific Analysis Services cube. The connection string you see in Excel is for an ADOMD.NET connection. 

AMO is a managed data provider used for server administration and data definition. For example, SQL Server Management Studio uses AMO to connect to Analysis Services." (https://msdn.microsoft.com/en-us/library/dn141152.aspx)

On one of my servers I have installed Excel 2013 and both SQL Server 2012 and SQL Server 2014. From this server I create a connection in Excel to SSAS 2012 located on a different server.

At C:\Program Files\Microsoft Analysis Services\AS OLEDB I can find both msolap110.dll (for SQL 2012) and msolap120.dll (for SQL 2014) files.

At C:\Program Files (x86)\Microsoft.NET\ADOMD.NET I have 3 ADOM.NET .dll files: 100, 110, 120.

So when I try to create a connection to SSAS in Excel 2013, it chooses by default the latest MSOLAP provider: MSOLAP.6.

But in Sharepoint 2013, the MSOLAP.6 data provider is not registered as a Trusted Data Provider.

Instead, MSOLAP.5 data provider can be used.

More about OLE DB providers:

https://msdn.microsoft.com/en-us/library/dn140245(v=sql.130).aspx

https://msdn.microsoft.com/en-us/library/ee210608.aspx

https://msdn.microsoft.com/en-us/library/dn141152.aspx

Solution:

To solve this issue permanently, MSOLAP.6 data provider should be registered in Central Administration Web site > Excel Services Application Trusted Data Providers.

Workaround:

If you have no access to the Central Administration Web site, you can still solve this issue using one of the following ways, but not as a permanent solution.

1. Modify the Connection string in the Excel file:

If on the server both msolap110.dll and msolap120.dll exist, the connection string can be modified and saved:

• Uncheck "Always use connection file" box
• Set Provider=MSOLAP.5
• Ok to save
• Upload the file on Sharepoint and Refresh All Connections. You should have no error.

2. Modify the Connection file used by the Excel file:

If you uploaded the file and the connection file on Sharepoint, you can still download a copy of the connection file and save it locally to modify it:

• Open with Notepad
• Look for connection string and set Provider=MSOLAP.5

</xml><xml id=msodc><odc:OfficeDataConnection

  xmlns:odc="urn:schemas-microsoft-com:office:odc"

  xmlns="http://www.w3.org/TR/REC-html40">

  <odc:Connection odc:Type="OLEDB">

   <odc:ConnectionString>Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Data Source=ServerName;Initial Catalog=DatabaseName</odc:ConnectionString>

   <odc:CommandType>Cube</odc:CommandType>

   <odc:CommandText>CubeName</odc:CommandText>

  </odc:Connection>

 </odc:OfficeDataConnection>

</xml>

• Save the file and uploaded again on Sharepoint to replace the initial one.
• Open the Excel file in browser and Refresh All Connections. You should have no error.

SQL Server Analysis Services connection troubleshooting

I have a server with 5 SQL Server instances installed, different versions from 2005 to 2014.

I have only one instance of SQL Server Analysis Services installed, 2012 version, which is not the default instance.

All SQL services (including SQL Browser) are running under domain user accounts and are up.

I tried to connect to Analysis Services from another server and I received the error below:

TITLE: Connect to Server

------------------------------

Cannot connect to computer_name\instance_name.

------------------------------

ADDITIONAL INFORMATION:

A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

------------------------------

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 192.168.00.00:2382 (System)

------------------------------

BUTTONS:

OK

------------------------------

As I looked to solve this connection problem, I found out some interesting things and also the solution. 

Facts:

The default instance of Analysis Services listens on port TCP 2383.

The other instances (named instances) have assigned a dynamic port which changes every time SQL Server Analysis Services is restarted.

SQL Browser for Analysis Services listens on port TCP 2382.

So, in order to connect to Analysis Services from another server, Firewall inbound rules must be configured as following:

• Inbound rule for port TCP 2383 (in case of SQL Server Analysis Services default instance).

OR

• Inbound rule for port TCP 2382 (used by SQL Browser) and
• Inbound rule for port TCP used by Analysis Services named instance.

And here comes the problem.

In order to configure inbound rule for the port used by Analysis Services, a fixed port should be configured.

And we have 2 simple ways: SSMS and configuration file.

Before we start:

To see the port currently used by Analysis Services:

• Check Process ID (PID) of the service in Configuration Manager
• Open Process Explorer and look for the PID. The process name is msmdsrv.exe. Double click on the process and a window will open. You'll find the port under TCP/IP tab.

OR

• Open Command Prompt and run netstat /abo. Look for msmdsrv.exe and there you have the port.

Solution 1: SQL Server Management Studio /SSMS

It's the simplest way to set the port.

Open SSMS > connect to Analysis Services > right click on Analysis Server and click Properties > the following window will open:

In Select a page tab choose General.

In Name column look for Port  and look at the values: 0 means a dynamic port is configured.

To fix the problem choose a port and write it under the column "Value".

Press Ok and Restart Analysis Services service in Configuration Manager.

Solution 2: Configuration file

Good to know:

As I said at the beginning, my SQL services are running under domain users accounts.

When SQL Server Analysis Services 2012 is installed, a group with the following name is automatically created (look in Computer Management > Local Users and Groups > Groups):

SQLServerMSASUser$computer_name$instance_name

Group Description: Members in the group have the required access and privileges to be assigned as the logon account for the associated instance of SQL Server 2012 Analysis Services.

Members: NT SERVICE\MSOLAP$instance_name - seems to be a virtual account created during SQL Server Analysis Services installation. In Process Explorer, under msmdsrv.exe > Security, this user is owner of Analysis Services service.

We have 2 different configuration files to look in (check the path on your server):

1. C:\Program Files\Microsoft SQL Server\MSAS11.instance_name\OLAP\Config\msmdsrv.ini

Permissions:

• The group SQLServerMSASUser$computer_name$instance_name has full rights on this file.

Look for <Port>0</Port>. A value of 0 means dynamic port.

2. C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini

Permissions:

• The group SQLServerMSASUser$computer_name$instance_name has full rights on this file;
• SQL Browser service account has full rights on this file.

Look for <Port>0</Port> and <PortIPv6>0</PortIPv6>. A value of 0 means dynamic port.

To change the port used by Analysis Services, open the first file (msmdsrv.ini) and look for <Port>0</Port>:

<RequestPrioritization>

<Enabled>0</Enabled>

<StatisticsStoreSize>1024</StatisticsStoreSize>

</RequestPrioritization>

<Port>0</Port>

<InstanceVisible>1</InstanceVisible>

Change the value of 0 into the port you want (Ex. <Port>49201</Port>).

Save and close the file. Restart the service in Configuration Manager.

!!!Check the second file (msmdredir.ini). You will see that the configured port was updated here too.

<Instances>

<Instance>

<Name>instance_name</Name>

<Port>49201</Port>

<PortIPv6>49201</PortIPv6>

</Instance>

</Instances>

Conclusion:

A dynamic port means that it will change each time the service is restarted. When Analysis Services service starts, the group SQLServerMSASUser$computer_name$instance_name check the system to see which ports are available and modify the first file msmdsrv.ini, assigning an available port.

When we connect to Analysis Services (named instance) from a different server using the instance name, the application (SSMS, Visual Studio etc.) ask SQL Browser (on port TCP 2382) for the port used by Analysis Services.

SQL Browser then reads the second file msmdredir.ini to see the port.

If a fixed port for Analysis Services is configured in the first file msmdsrv.ini and Firewall inbound rules for both SQL Browser port and Analysis Services fixed port are opened, the connection should work without problems.

AlwaysOn Availability Groups configuration - easy steps to follow

AlwaysOn Availability Groups, introduced in SQL Server 2012, is the latest feature from Microsoft for providing high-availability and disaster recovery solutions.

The feature is available only for Enterprise edition of SQL Server 2012 and SQL Server 2014 and combines Windows failover clustering with SQL Server database mirroring.

Regarding the number of nodes in AlwaysOn Availability Groups, SQL Server 2012 supports a maximum of four secondary replicas and SQL Server 2014 supports a maximum of eight secondary replicas.

I won't enter into details regarding the definition or the configuration of AlwaysOn Availability Groups, instead I will point the steps to follow and to consider when configuring it.

Step 1. Configure Windows Server Cluster (a feature of Windows Server operating system).

Step 2. Install SQL Server Database Engine (2012/2014 enterprise edition) on each node:

• Installation as New SQL Server stand-alone installation (not in failover cluster mode);
• SQL Server service accounts configured to Windows domain users;
• All instances should have the same name;
• All instances should have the same collation;
• Make sure each instance has the same path for user databases (data files and log files). Not the same disks for storage, but the same configuration and path.

Step 3. Choose a shared location for backups and give permissions on it to all SQL Server database engine service accounts.

All instances configured in AlwaysOn need to have access to databases backups.

Step 4. Make sure each SQL Server service account has permissions on all related datafiles and logfiles folders.

Step 5. Configure static port on each instance and restart the services (if it's not the default instance).

Step 6. Open inbound rules for the static ports in Windows Firewall.

Step 7. Enable AlwaysOn on each instance in Configuration Manager and restart the services.

Step 8. Go to primary replica (the node you choose as active/primary) and prepare the database:

• Set recovery model to FULL;
• Backup full the database to the shared backup location (on which all service accounts have permission).

Step 9. Run New Availability Group Wizard on the primary replica:

• The wizard creates endpoints for each instance;
• Add inbound rules in Windows Firewall for the endpoints ports on each server;
• Choose the databases you want to add to Availability Groups.

Step 10. Add a Listener to the Availability Group and configure it:

• Assign an unique name within Active Directory;
• Assign a new, unused IP in the chosen subnet;
• Assign an available port (the default port is TCP 1433, but if it is used by MSSQL or another application, assign another);
• Add inbound rule in Windows Firewall for the Listener port on all servers.

And this is all.

Other settings like Initial Role, Automatic Failover, Readable Secondary etc.  can be done during the initial configuration and after, but taking in consideration the steps above will make it work.