Post reply

Select Permission denied

  • July 27, 2004 at 3:55 am

    #163023

    Hi,

    I have just moved servers and have some permission issues.  On the old server I've a user login that can select from a particular table, but on the new server he is denied. 

    If I explicitly "grant select" of course he is OK, but that does not make sense why he lost this in the transfer - there is no tick on the select column on the other server and it works OK.  In fact I'm confused what the permissions box means when it has neither tick or cross?

    I really want to know what might have gone wrong as I'm sure he won't be the only one and I don't wan't to have to tick columns for every user.  I have run sp_helprotect  on the object and it just says there are no matching rows.  And i donb't believe he's an orphaned user either.

    Appreciate your thoughts on this, particularly what the permissions a user has on an object when he's neither tick nor cross!

    Rob

  • July 27, 2004 at 4:36 am

    #516249

    the 3 status of any permission are

    GRANT - tick

    DENY - cross

    REVOKE - empty

    is the user on the new server a member of other databases ? was he on the last server. Did he have a server based role (such as server admin)

    if you are setting up custom permissions for users then you should really set up a group and then set the permissions for the group. then you can add users to the group.

    this makes things a lot easier.

    MVDBA

  • July 27, 2004 at 4:55 am

    #516254

    Hi thanks for that I didn't know a no tick meant revoke. 

    He is a member of other databases, in the same way he was on the old server.  He belongs to some groups I have set up which help organise users into collections e.g. managers, and users as well.

    If his permissions are blank, meaning revoked that really means that the public role permissions is taking precedent I assume?

    Rob.

  • July 27, 2004 at 6:54 am

    #516280

    cumulative permissions for all his roles in the database.

    so public + other roles + specific user permissions + server permissions

    MVDBA

  • July 27, 2004 at 8:47 am

    #516316

    Correct. Unless there's an explicit deny along the way. It trumps everything.

    K. Brian Kelley
    @kbriankelley

  • July 27, 2004 at 8:52 am

    #516321

    or unless they're in Sytem administrators group (or a member of local or domain ADMINS for the builtin/admins)

    the SA role and login will override all DENY statements

    MVDBA

  • July 27, 2004 at 8:58 am

    #516323

    Well I've been stumped on this one, I've had to grant him explicitly select permissions despite not finding any deny's on the object.

    In another permissions problem I'm unable to find why someone can not delete from a table (which is done via a stored procedure in another database).  They have exec rights on the sproc which does the delete but I still have had to grant them seperate delete rights on the table refrenced in the sproc. There doesn't seem to be any deny's on the table at all.  And they're not in a deny_data reader role.

  • July 27, 2004 at 9:09 am

    #516327

    permissions on a view or Stored procedure do not infer permissions on the table they access - you need to specify permissions for the supplying object as well.

    it's better to define your permissions clearly in your roles rather than just leaving everything blank. - that way you can trace these "bugs" more easily.

    MVDBA

  • July 27, 2004 at 9:19 am

    #516334

    This depends. If the stored procedure/view and back-end table are both owned by the same user, ownership chaining is in effect. They only need permissions on the first object. This ensures we don't have to give permissions to the object being referenced.

    Which reminds me, are all the objects owned by the same owner?

    K. Brian Kelley
    @kbriankelley

  • July 27, 2004 at 9:19 am

    #516333

    Oh, I thought if you had permissions to execute a stored procedure, then you could run it and that sproc would do anything for you it was supposed to do, such as delete, insert, select etc.  Even if you didn't have explicit access to those objects.

    Thanks.  Rob.

  • July 28, 2004 at 1:21 pm

    #516656

    Hi Firthr!

    You write that the user had access to the table before you moved it into a new server. Did you re-create the user on the new server, or did you bulk in users from the old server?

    If you created a new user and then restored the database that he/she had access to, the user wont have the same sid anymore. Same username but different sids.

    If you, in the database that the "lost" access is run:

    sp_change_users_login 'report'

    You will find users that are present in the database, and as logins on the server without matching sids. Have a check in book online how to fix those.

    About the exexute right on a stores procedure and it's underlaying tables, I do completly agree about the owner change that has to be the same if you don't use seperate rights on the underlaying objects. But if a stored procedure or view use tables/views/sprocs in another database on the server you have to explicit set correct rights on those objects in the other database(es).


    robbac
    ___the truth is out there___

  • July 29, 2004 at 3:12 am

    #516724

    yes all the objects and stored procedure are owned by the dbo.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply