How to assign the System Administrator role to a user through SQL?
Scenario:
When a database backup is restored from the Production to the Development environment, the user may not have the System Administrator role. In this scenario, the following steps can be followed to assign the System Administrator role to the user.
High level resolution steps
We can follow the below steps to Default parameters in SSRS report:
Navigate to SQL Server Management studio.
Create a new query for Database.
Run below mentioned Query.
Detailed resolution steps
Step 1: Navigate to SQL Server Management studio
Step 2: Create a new query for required database
Step 3: Execute the below command
Select security role table using the command
| Select RecId, * from SecurityRole |
Here note the recid of system administrator
you can insert into Security user Role table with userId with below command
| insert into SECURITYUSERROLE(user_,SECURITYROLE,ASSIGNMENTSTATUS,ASSIGNMENTMODE) values(‘Dheeraj’,171,1,1) |
Now we must insert user into UserInfo table if the user is not existing
| insert into USERINFO(ID,NAME,NETWORKALIAS,ENABLE) values(‘Dheeraj’,’Dheeraj’,’dheerajg@sainacloud.com’,1) |
OUTPUT
Navigate to System Administrator -> Users -> Users
New users will be created with the system administrator role.
