Post reply

equivalent of sp_change_users_login for roles

  • December 31, 2004 at 9:12 am

    #87613

    I have an application role in a newly restored database that is saying doesn't exist. Normally when I have this problem with 'users' I run the sp_change_users_login storedproc to fix it.....Is there an equivalent one for "roles" if not, how do I go about deleting and re-adding this role and simply getting the role to be seen by the database (I can see it in Enterprise Manager)

    Thanks

  • December 31, 2004 at 9:23 am

    #535513

    Is the role in sysusers? Are there child rows in syspermissions?

     

  • December 31, 2004 at 9:35 am

    #535518

    no, it is not in sysusers...

    i can only see it under 'roles' under the database....

    when i try to delete it, it gives me the error that it doesn't exist...

     

  • December 31, 2004 at 9:46 am

    #535520

    In Enterprise Manager what is the Role Type? Is it listed as Application? If so, use sp_dropapprole.

    K. Brian Kelley
    @kbriankelley

  • December 31, 2004 at 9:51 am

    #535522

    It's an application role but when i try to either delete it from enterprise manager or run 'sp_dropapprole' from query analyzer, i get the following error:

    Server: Msg 15014, Level 16, State 1, Procedure sp_dropapprole, Line 29

    The role 'mdconnections' does not exist in the current database.

    I am pointed at the right db too.

     

  • December 31, 2004 at 10:00 am

    #535524

    OK, I figured it out in the end.....I couldn't re-add it in Enterprise Manager, because I could see it there...I ended up re-adding the role through Query Anlayzer successfully and all is ok.

     

    Thanks!

  • December 31, 2004 at 10:06 am

    #535526

    When you execute sp_helprole on it, does anything show?

    Something's not right because all Enterprise Manager does here is execute sp_helprole without parameters. It then executes follow on queries to get the uid of the roles in case you select on one. The system stored procedure sp_helprole issues queries against sysusers only but you said it wasn't in sysusers.

    If you close out EM and go back into it, do you see the phantom role?

    K. Brian Kelley
    @kbriankelley

  • December 31, 2004 at 10:07 am

    #535527

    OK. Was it just a refresh issue on EM?

    K. Brian Kelley
    @kbriankelley

  • December 31, 2004 at 11:09 am

    #535532

    well i tried refreshing within EM and that didn't seem to make the role go away (when it didn't exist).

    so i ran the add role in QA and then went back to my EM window, refreshed again, no change in the view but I could then use the role.

    it was similar to when i have the problem with a "user" and i end up running the sp_changeuserslogin.

Viewing 9 posts - 1 through 8 (of 8 total)

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