75,730
Here is a first. Client is upgrading from GP 2015 to GP 18.4.1384. They removed almost all rights to 'sa' in GP. So when I tried to log into GP on a new server I can log in as 'sa' but I cannot do anything. I cannot log as any of the users because the passwords have changed. Is there any way to set 'sa' as a PowerUser on the new server?
Hi Richard
You will need to fix this via the backend in SQL.
Using GP Power Tools' Resource Finder I have confirmed that there are only two tables you need to look at.
select * from SY09100 where SECURITYROLEID = 'POWERUSER'
Security Role ID Security Role Name Security Role Description Security Role Type Created User ID Created Date Modified User ID Modified Date Workflow Status Security Role Name Modified Security Role Description Mod Workflow Origin Company
POWERUSER POWERUSER POWERUSER 1 sa 6/10/2020 1/01/1900 00:00:00 9
select * from SY10500 where SECURITYROLEID = 'POWERUSER'
User ID Company ID Security Role ID
DYNSA -1 POWERUSER
DYNSA 1 POWERUSER
sa -1 POWERUSER
sa 1 POWERUSER
I think what they did was create a user called PowerUser. It appears that their reseller was playing around with security in the back-end.
I'm not sure then........the SY10500 table is what security roles are assigned to the users.
Other than that, searching the system tables for 'POWERUSER' the only other tables mentioned are SY09100 which is more a description of the security role and then the SY10500.
Unlike other security roles, it doesn't even exist in the SY10600 table, as PowerUser role is more hard-coded within the dictionary files to give users access to everything.
I'm curious as to how they would've done what they apparently have done, by removing all permissions from the PowerUser security role?
Both sa and DYNSA are in the SY10500 as POWERUSER. The problem is they have removed POWERUSER from both of these. Is there another table that assigns the roles?
If not, they will need to go into the old GP and reassign and then take fresh backups.
Does the SY10500 table show both DYNSA and sa as POWERUSER for each company database present in the SY01500 table?
I haven't tried manually inserting into this table, if you don't have a PowerUser that you can login to GP and grant via the User Security window.
If you're unable to login to GP as 'sa' due to the password changing, can you reset the sa password in SSMS and then login to GP? the sa password isn't encrypted like the other GP users, so we can change the password in SQL for 'sa' while we cannot without being prompted to change the password again when logging into GP, like other users.
Let me know if this helps or not, or we can see what other options we may have.
Thanks
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,554
Super User 2024 Season 2
#2
Martin Dráb
228,588
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (