October 3, 2011 at 3:30 pm
So, in trying to deal with other issues, I've discovered that an attached database is missing a ton of system stored procedures, including "sp_addrolemember".
It's quite possible this is a very old database, that has been upgraded over the years, moved to new servers, etc.
My question is not so much "how on earth did these things go missing", but more "how the heck can I get them back?"
I've googled to no effect... apparently I'm the only person in the world who has ever stumbled across this. Does anyone have any idea how to restore all the system stored procedures to a new database that has been restored to SQL Server 2008 and is apparently missing them??
Thanks!
October 3, 2011 at 4:02 pm
I might be mistaken but those system sprocs are stored in a special system database now (as of 2005) and master before in 2000 and before. You not finding it should not indicate a problem.. You should be able to
USE databasesname
GO
EXEC sp_addrolemember blah, blah
GO
and it should work, why don't you explain that problem you are having because missing system sprocs probably isn't one of them.
CEWII
October 4, 2011 at 9:18 am
When I USE the database, and try to run the stored procedure, I get this:
Could not find stored procedure 'sp_AddRoleMember'.
If I open the database, open stored procedures, and open "system stored procedures", I do not see it.
If I go to another database, open stored procedures, and open "system stored procedures", I see it just fine.
I'm doing this with a user in the SysAdmin role, so it can't be security hiding it from me. I'm just confused as to what went wrong, and how to correct whatever it is that went wrong.
October 4, 2011 at 12:56 pm
Hm, odd, on my databases I see a sys.sp_addrolemember but not a dbo.
the way sp_ sprocs are supposed to work is that if it doesn't find a local version it goes to master and tries there.
Are you using a case sensitive collation by chance? Where 'sp_AddRoleMember' is different from 'sp_addrolemember'..
CEWII
October 4, 2011 at 2:01 pm
No, it's all case-insensitive. It's an older database. It just struck me as really weird that no matter what I did, I kept getting the error. Go to a different database on the same server, and it worked fine.
I was just hoping there was some easy way to 'restore' all those system stored procs to the database, somehow.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply