You can assign report writer role to any user you (as administrator), it does not depends on ITSM end.
Thank you for your reply. I tried keeping this default role but not all users were assigned as Report Writer. We can assign this role one by one to users but is there any other way to assign this role to all users at once?
I doubt until BMC folks share direct sql update on db level. As far as i know you have to add them manually.
But you should be able to set default in future. I will try to test in the morning when get to office and share.
I'm interested in this as well. If a customer has a need to give 500 users the "Report Writer" role, for example: Is the customer expected to do this MANUALLY?
I sent a request to support and asked if I can get the DB-tables where the user-role relationships are stored. Will report back when I hear something.
3 of 3 people found this helpful
OK, my colleague Maximilian Jost did some digging around in the SmartReporting-DB and found something:
- The table "OrgRole" seems to contain a list of all roles which can be assigned to a user.
- The table "Person" seems to contain all users. On our system, most users are listed more than once, but I'm not sure if that's normal or not.
(We screwed around with this particular environment a while ago, so that may or may not be the cause ).
- The table "IpClass" seems to contain users' Logon-IDs (more on that later).
- The table "IpContact" seems to contain users' email addresses.
Now, let's combine all those findings and look up the current role of a user based on the user's first and last name
and display his Logon-ID and email address as well:
(All SQL-queries listed here are for MSSQL. If you're on Oracle, adapt where needed.)
select c.EmailLeft, d.EmailAddress, b.FirstName, b.LastName, a.*
from StaffMemberRole a
join Person b
on a.IpEmployee = b.IpPerson
join IpClass c
on a.IpEmployee = c.IpId
join IpContact d
on a.IpEmployee = d.IpId
where b.LastName = 'Smith' and b.FirstName = 'John'
AND a.EndDate = '9999-12-31 00:00:00.000';
Note: You could replace the "where"-line with the following line if you want to look up the user based on the ID in the "Person"-table:
where a.IpEmployee = '12345'
or replace it with the following if you want to look up the user based on the Logon-ID:
where c.EmailLeft = 'jsmith'
(On my environment, the "EmailLeft"-value is the Logon-ID. I'm not sure if that's the case on every system or if this is truly always the
left portion of the email address)
or look it up based on the email address:
where d.EmailAddress = 'email@example.com'
Now the question is: What happens on the DB when you change a user's role through the Smart-Reporting-Admin-GUI? (If we can find that out, then we could easily script the role-assignment and do bulk-updates)
Two things happen!
A - The currently active entry in "StaffMemberRole" (which you can easily identify: It has a value of "9999-12-31 00:00:00.000" in the
"EndDate" column) is deactivated by having the "EndDate" value changed into the current date.
B - A new entry is created in the "StaffMemberRole" table with the new role and an EndDate of "9999-12-31 00:00:00.000".
So let's say you'd like to give user "jsmith" the role "Report Writer". Here are two queries which perform steps A and B:
We deactivate the current role-assignment:
SET EndDate = CONVERT(varchar(10), GetDate(), 120) + ' 00:00:00.000'
WHERE IpEmployee = (
select IpId from IpClass a
where a.EmailLeft = 'jsmith'
AND EndDate = '9999-12-31 00:00:00.000';
We create a new role-assignment with the role we want to assign now:
INSERT INTO StaffMemberRole
(IpEmployee, IpEmployer, RoleCode, StartDate, EndDate, IpAccessOrg)
VALUES ((select IpId from IpClass a where a.EmailLeft = 'jsmith'), '1', 'YFREPORTWRITER', CONVERT(varchar(10), GetDate(), 120) + ' 00:00:00.000', '9999-12-31 00:00:00.000', '1');
And that's it!
All of this is, of course, absolutely unsupported.
Thanks for sharing, please make sure it will not break anything, well test the solution. But I will say this is not the way customer find the solution, I have always said Smart Reporting was premature project which was released. It does not have proper users administration, Personaly would be ideal when we create or update user and assign permissions there should be report roles to assign to user and sync job should sync them, also check which users need to be sync (initially everyone , now only with fixed and floating license users).
Smart Reporting User, User Groups, Roles management and Misc. I had this idea but no one voted and BMC ignore the good ideas as usual when they integrate third part software like in this case YellowFin called Smart Reporting.
3 of 3 people found this helpful
I have some news from BMC support. Their reponse:
We have performed couple of tests regarding this case and you can use the below query to update your Roles using DB Query.
-- Below query will update Roles for all the Users Except Administrators
set RoleCode = '<ROLE CODE>'
where RoleCode != 'YFADMIN'
Available Role Codes to update:
-- Corp Writer - YFCORPWRITER
-- Report Writer - YFREPORTWRITER
-- Report Consumer - YFREPORTCONSUMER
-- Dashboard Consumer - YFDASHBOARDCONSUMER
Development has tested it inhouse and it is working correctly.
This is a better answer than I expected.
When I pointed them to this thread and asked them for their opinion, they responded:
It is not officially supported or recommended by BMC.
So you can test these details on own risk by taking DB backup.
I created a new idea, let's see if we can get some traction on this:
3 of 3 people found this helpful
Update for Smart Reporting 18.05:
The Role names (OOB in SR Admin console) and StaffMemberRole RoleCodes are:
- Consumer & Collaborator - YFREPORTCONSUMER
- Dashboard Consumer - YFDASHBOARDCONSUMER
- Personal Content Writer & Collaborator - YFREPORTWRITER
- Public Content Writer & Collaborator - YFCORPWRITER
- Public Content Writer & Collaborator - Advanced - PUBLICCONTENTWRITERCOLLABORATORADVANCED
- System Administrator - YFADMIN
Also, the date format seems different, or is differently displayed on my Oracle SQL Developer.
This was the format I had to use to find a current role value:
to_date(enddate) = '31-DEC-99'
1 of 1 people found this helpful
To change all current Dashboard User access to another role (in this case Public Report Writer & Collaborator):
set rolecode = 'YFCORPWRITER'
where rolecode = 'YFDASHBOARDCONSUMER' and
to_date(enddate) = '31-DEC-99';
Note the use of to_date to translate the date value and the check for enddate = '31-DEC-99'. This makes sure you only change the current values.
Also the current rolecode is checked specifically so you only change the values you want to change (rather) than any non-admin, as in the BMC suggested statement.
Hi Mohammad Rehman,
I have one question regarding same context.
Right now we are working on BMC Smart reporting 19.02, created custom role which is actually copied and advanced version of Personal Content Writer and Collaborator and keep it as Default.
I observed, whenever a new user got added in Smart Reporting, by default user getting Dashboard Consumer role though the default one is custom role.
Please suggest, how can I make it possible custom role as Default one for all upcoming new users in Smart Reporting
Thanks in Advanced,
2 of 2 people found this helpful
User sync utility i.e. RSR:UserSync does a API call which is set to default to Dashboard Consumer. I think this is not publicly open to change it.
In such case, you will need to perform steps as mentioned by Kelly Logan.