December 31, 2004 at 9:12 am
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
Is the role in sysusers? Are there child rows in syspermissions?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 31, 2004 at 9:35 am
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
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
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
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
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
OK. Was it just a refresh issue on EM?
K. Brian Kelley
@kbriankelley
December 31, 2004 at 11:09 am
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