Skip to Content

Permission denied even after role is granted

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 28, 2017 at 08:50 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Feb 27, 2017 at 03:58 PM

    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).

    Add comment
    10|10000 characters needed characters exceeded

    • 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