Skip to Content
0

Permission denied even after role is granted

Feb 27, 2017 at 11:22 AM

106

avatar image

We are assigning a role permission to create database. Same role is assigned to a login user, but when we try to create a new database using the login user to which the role is assigned we get permission denied error. I tried this with 15.5 and 16. Are we doing something wrong here? Do we have to take care of any other parameters? We went through this discussion

https://archive.sap.com/discussions/thread/3932505

Any help is appreciated. Thanks in advance.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Mark A Parsons Feb 28, 2017 at 08:50 AM
1

Not sure I understand what you're trying to do.

In your original post you mention trying to grant permission to create a database, but in your next post you mention granting select on the syssrvroles table. These 2 operations have nothing in common, ie, select access on syssrvroles does nothing for you in terms of being able to create a database with a non-sa_role user.

Attached are files demonstrating 2 different ways to grant create database to a login (bob):

- grant create database to bob: bob-create-db.txt

- grant create database to a role assigned to bob: bob-role-create-db.txt

If you're still having problems granting create database to a login then you'll need to post back here with the complete set of commands (and results), ie, you'll need to reproduce the problem.


Show 3 Share
10 |10000 characters needed characters left characters exceeded

Sorry for the confusion, but my main concern is when I try to create a role and assign select permission/create database permission and assign the same role to a login user than the user still gets permission denied. Basically the roles that are assigned to the user are not working.

0

Review those attachments I provided in my previous post.

Compare what I did with what you're doing to see what's different.

Better yet, implement my examples on one of your dataservers to verify it works.

1

Hi Mark,

In the first txt file you have directly assigned the create database to the login user whereas in the second you have first assigned the role to the user and than assigned the permission to the role. Both of these worked for me, though I am not sure is both of these approaches will work for me. You may be right, Sybase Central may be having some issues. Thanks for you help.

0
Mark A Parsons Feb 27, 2017 at 03:58 PM
0

Did you make sure the login in question logged out and back in after the granting of the role/permission?

Did you make sure the login in question was added as a user in the master database?

Did you make sure the role was active? [By default user-defined roles are not activated at login.]

Do you have these same problems if you use the isql command line tool? [Want to rule out any issues with any GUIs you may be using.]

--------------

If you're still having problems ...

- post all of your commands (create role, create login/sp_addlogin, sp_adduser, grant, etc), plus the command generating the permissions error, plus the full text of the error message.

- please post the output from sp_displaylogin <login>, sp_displayroles <login>; also make sure exec master..sp_helprotect <login> shows create database in the output

NOTE: I'd recommend placing all of your output from the above into a *.txt file and attach to your reply/post (to maintain formatting and make it easier to review the output).

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Mark,

Did you make sure the login in question logged out and back in after the granting of the role/permission? Yes

Did you make sure the login in question was added as a user in the master database? Yes

Did you make sure the role was active? [By default user-defined roles are not activated at login.] Yes

Do you have these same problems if you use the isql command line tool? [Want to rule out any issues with any GUIs you may be using.]

I have tried to grant the role using isql and it succeeds. The process that I follow is

--create login user.

--create master database user.

--create role and grant select permission on particular table(syssrvroles).

--grant this role to the login user.

I can see this role is granted to the login user but the associated database user does not have the permission on that table. I granted this permission manually using Sybase Central to the database user and now it fetches data from the table(syssrvroles) without any issues.

Am I doing anything wrong? I will try to get the scripts you have mentioned.

Thanks for helping.

1

Hi Vikas,

Can you please send the output of below:

1> sp_activeroles                                      (for the user who is intended to create db, USER must be logged in)
2> go

AND

1> sp_displaylogin <username>                                      (for the user who is intended to create db)
2> go

Regards

Pankaj

0

1.

<Role-name>

2.

  • Suid: 110
  • Loginame: <LoginUserName>
  • Fullname:
  • Default Database: master
  • Default Language: us_english
  • Auto Login Script:
  • Configured Authorization:
  • <RoleName>(default ON)
  • Locked: NO
  • Date of Last Password Change: Feb 26 2017 11:51PM
  • Password expiration interval: 0
  • Password expired: NO
  • Minimum password length: 6
  • Maximum failed logins: 0
  • Current failed login attempts: 0
  • Authenticate with: AUTH_DEFAULT
  • Login Password Encryption: SHA-256
  • Last login date: Feb 28 2017 2:00AM
  • Exempt inactive lock: 0
  • Execution time: 0.081 seconds
0