Advanced Report Gallery

Report Name:

Show Owners of all Secrets

Report Description:

Shows all the owners of Secrets in Secret Server.

Published 2/7/2012 Contributed By Jonathan Downloads 93

SELECT udn.DisplayName AS [User] ,ISNULL(fp.FolderPath, N'No folder assigned') as [Folder Path] ,s.SecretName AS [Secret Name] ,st.SecretTypeName AS [Secret Template] ,gdn.DisplayName AS 'User/Group' ,CASE gsp.[Inherit Permissions] WHEN 'No' THEN 'Secret' WHEN 'Yes' THEN (CASE f.EnableInheritPermissions WHEN NULL THEN 'Folder' WHEN 1 THEN 'A Parent Folder' WHEN 0 THEN 'Folder' END) END AS [Permissions On] ,s.SecretId FROM tbSecret s WITH (NOLOCK) INNER JOIN tbGroupSecretPermission sgp WITH (NOLOCK) ON s.SecretId = sgp.SecretId AND sgp.PermissionID = 3 INNER JOIN tbUserGroup ug WITH (NOLOCK) ON sgp.GroupId = ug.GroupId INNER JOIN tbSecretType st WITH (NOLOCK) ON s.SecretTypeId = st.SecretTypeId LEFT JOIN vFolderPath fp WITH (NOLOCK) ON s.FolderId = fp.FolderId LEFT JOIN tbFolder f WITH (NOLOCK) ON s.FolderId = f.FolderId INNER JOIN vUserDisplayName udn WITH (NOLOCK) ON udn.UserId = ug.UserId INNER JOIN vGroupSecretPermissions gsp ON sgp.GroupId = gsp.GroupId AND s.SecretID = gsp.SecretId INNER JOIN vGroupDisplayName gdn WITH (NOLOCK) ON gsp.GroupId = gdn.GroupId WHERE s.Active = 1 AND st.OrganizationId = #Organization ORDER BY 1, 2, 3, 4, 5, 6, 7