JP Voogt A Data Enthusiasts Ramblings

PDW - Currently Assign User Permissions


Let us jump right in, the first thing that we need to know is that the APS/PDW is not like a normal SQL Server instance. It does look and feel like one, but it has its own little quirks. One of the big ones is that it is not as straight forward to see current assigned User Permissions as what one would expect.

Code Snippet

SELECT AS UserName, 
		object_name(major_id) AS ObjectName
FROM sys.database_permissions AS DP 
JOIN sys.database_principals AS DPUsers ON DP.grantee_principal_id = DPUsers.principal_id 
WHERE not like '##%' 
AND not like 'NT SERVICE%' 
AND not like 'NT AUTHORITY%' 
AND != 'public' 
and not like 'l_cert%' 


The above code snippet will return you with the Username, The Granted Permission and the ObjectName if only granted on a specific object.

Disclaimer: Content is accurate at the time of publication, however updates and new additions happen frequently which could change the accuracy or relevance. Please keep this in mind when using my content as guidelines. Please always test in a testing or development environment, I do not accept any liability for damages caused by this content.

If you liked this post, you can share it with your followers or follow me on Twitter!