SQL2K new server permissions problems - HELP!

[wbodger]

So, last night our production box died. I restored it long enough to detach my 4 databases and get them off there and onto a new box. Got the box up and going, but now the stored procedures don't work. I get errors such as

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040001)
Error in Login::GetLoginResult: 0 : #-2147217911-[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'wiplctCustomerLogin2', database 'Onyx', owner 'dbo'.

I have verified that all of the users exist on the new machine. I checked the permissions on a few stored procedures and it says they are right, but they don't work. I have now been up all night, so I am perhaps missing the obvious, but... Anybody out there got any pointers or tips for me?

Willie

 

[intelwizrd]

Check the user mapping between the users defined on the system and the users defined in the DBs. open the user up in the security folder for the server and see if it is mapped to the database. if not and you try to map it, you may see an error like (in 2005)

User, Group or role 'username' already exists in the current database. (Microsoft SQL Server, Error: 15023)

We have a similar problem all the time restoring databases to different servers even though the user name is the same, its not. anyway, if you have specific user permissions set on the database, open the user under the database security folder and copy the permissions, then delete, open the user under the server security folder, map the user to the database with permissions and see if that works.

I feel your pain, been there before.

----------------------------
Josh

 

[intelwizrd]

Just saw that you said the server was 2k in the thread title. Still should be relevant, we noticed the problem when we were running 2k servers and that it still exists in 2k5.

----------------------------
Josh

 

[wbodger]

Thanks for the info, Josh. I found a script that actually took care of my orphaned users, called sp_fixusers that 'The Chadinator' wrote. It took care of my problem nicely!

wb