Search code examples
reporting-servicesssrs-2008

SSRS - Users Table

I have done some digging but am unable to find out specific information about the Users table that is created in the ReportServer database?

Firstly I wanted to check what was the point of entry for users going into this table? In our table it looks like virtually ALL our domain logins exist in this table... Which leads me to the next question... Are these actually linked to the domain accounts at all (I presume not). I just want to make sure that if a domain account is disabled that any subscriptions 'Owned' by that user will not fail on the next run attempt.

Any help on this relatively dark area is greatly appreciated.

Regards Chris

Solution

  • Before answering let me remind you that the SSRS database is not officially documented, so the following answers are only be based on my experience of the product so they can be wrong / incomplete.

    what was the point of entry for users going into this table?

    • The GetUserIDBySid and GetUserIDByName stored procedures, called by the GetUserID stored procedure.
    • The GetPrincipalID stored procedure, called by the UpdatePolicyPrincipal stored procedure.

    The name can be confusing, because in fact these SPs are not only getting the User ID.
    If the user does not exist in the database, they insert it.

    Now an additional question should come up:

    When are these SPs executed?

    The obvious answer is "when SSRS needs to get the User ID".
    This can be, for example, when:

    • Creating a policy for that specific user
    • The user execute a report
    • The user schedules a subscription

    If the user is part of a group that has access to a report and has never done any action needing to get his User ID, he should not be present in the Users table.

    Are these actually linked to the domain accounts at all

    No, if you delete the account from your AD it will stay in the Users table.
    The information that you can use if you need to link them for whatever reason are:

    • The User Login: UserName
    • The Security ID: Sid

    Bonus question/answer:

    What information does the Users table contains?

    • UserID: A generated GUID (NEWID())
    • Sid: The Security ID, if you need to find the Security ID from the User Login, you can use the SUSER_SID function
    • UserType: The UserType
    • AuthType: The LoginType
    • UserName: The NT User / Group login