Thursday, April 19, 2012 5:26 PM
When reviewing Users and Roles on our ReportServer db (SQL Server 2005), I've noticed some older users who reflect as having the role of browser on some reports/folders even though they are no longer members of the AD groups and in some cases no longer have AD accounts at all (We're using the Integrated Windows Authentication option and users are added to Appropriate AD groups which are then assigned to the appropriate roles on the Folders and reports within the reporting services website). I've looked through the Managing Permissions and Security for Reporting Services (http://technet.microsoft.com/en-US/library/ms156014(v=sql.90).aspx) which indicates the following for Windows Authentication:
On a report server, authentication through the default Windows security extension is performed by Internet Information Services (IIS). The user and group accounts that you specify in role assignments are created and managed through Active Directory. Only valid accounts can be specified. A report server verifies the validity of user and group accounts periodically. Role assignments that specify accounts that are no longer defined in Active Directory are removed. This action is logged as an information message in the application log file.
These users have been confirmed to be removed from the AD groups (and in some cases the user no longer exists in AD), and I've confirmed there are no User specific assignments on the folders and reports in question. Are there any specific settings that "Periodic" verification rely on (underlined portion of above snippet), or is there any documentation regarding how best to remove those roles from the users following a review?
Tuesday, April 24, 2012 7:22 PM
Well, for what it is worth, the more recent SQL Server documentation, such as the following link for SQL Server 2008, do not make that statement. http://technet.microsoft.com/en-US/library/ms156014(v=sql.100).aspx
My experience is pretty much that SQL Server is not aware of when a Windows login is deleted from the domain. Of course, if someone tried to make use of the login, it will fail at that time. However, it is common for user names to exist in a database long after the login is missing. Periodic clean up is useful. If you know that a login is invalid, there is no reason not to delete it. (If a login owns some other objects, you will need to change that ownership first.)
If at all possible, I prefer to grant rights to Windows groups rather than logins. That way I can have users added to or removed from the group without (usually) leaving logins and users lingering on the server or in the database. Likewise for Reporting Services you can use windows groups.
- Edited by Russell FieldsMVP Tuesday, April 24, 2012 7:23 PM
Tuesday, April 24, 2012 9:23 PM
We do assign access by Active Directory groups. We were hoping of producing a report we could use to help monitor the access groups/users have to various Datasources/Folders/Reports rather than relying on the UI. It appears that we may have to use a combination of the two for the review. I.E. Verify with our Network Admins that they no longer belong to the groups in question, and confirm in the UI that they don't exist anywhere as an individual with rights to a report (in a user folder for example).
I'm seeing both the groups and the individual users in the groups (assuming they have previously logged in and accessed the reports) listed when I query the server using the following query:
SELECT CASE IsNull(c.Path,'')
WHEN '' THEN 'Home'
when 1 then 'Folder'
when 2 then 'Report'
when 3 then 'Support Files'
when 5 then 'Data Source'
End AS [Type],
FROM dbo.Catalog c
INNER JOIN dbo.PolicyUserRole p ON p.PolicyID = c.PolicyID
INNER JOIN dbo.Roles r ON r.RoleID = p.RoleID
INNER JOIN dbo.Users u ON u.UserID = p.UserID
WHERE c.Type in (1,2,3,5)