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.