question

SKum42-3889 avatar image
0 Votes"
SKum42-3889 asked Joyzhao-MSFT edited

In SSRS how to distinguish between username accounts and group accounts.

I need a SQL Query to Extract data for all the reports which have security settings set to accounts by employee names(Usernames) and also the account status (disabled/deactivated/active).

Goals:

1) Differentiate Username(Login) Accounts from Group Accounts. ( Both hold roles in the security configuration)
2) Finding if the User Account is Active or Inactive

sql-server-generalsql-server-reporting-servicessql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @SKum42-3889 ,
What does "group account" mean? I am very confused about this. It's not very clear for me what you want to express.
Regards,
Joy

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Joyzhao-MSFT As you know in SSRS we can either give permission(Role) to an individual user directly by using their usernames or we can add that said user to a group and then give that group the access(Role), so that in the future if some other user wants a similar kind of access he/she can directly be added to the said group with access.

So I need to extract data for every Roleholder from the database...So when I extract data I get both usernames and groups. I only need to get the data for usernames and then check if the username is still active or not.

Regards,
Kum

0 Votes 0 ·

Hi @SKum42-3889
Try the following query:

 use ReportServer
 select C.UserName, D.RoleName, D.Description, E.Path, E.Name 
 from dbo.PolicyUserRole A
    inner join dbo.Policies B on A.PolicyID = B.PolicyID
    inner join dbo.Users C on A.UserID = C.UserID
    inner join dbo.Roles D on A.RoleID = D.RoleID
    inner join dbo.Catalog E on A.PolicyID = E.PolicyID
 order by C.UserName   

Use the following query to view the username:

 use ReportServer
 select UserName
 from dbo.Users

Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

0 Votes 0 ·