weird errors on new SQL Server 7.0 db...

[BeckahC]

I have a high traffic, high volume database currently on an NT Server box running SQL Server 7.0. All is well, however we are updating our server environments to 2K Server and updating some of the hardware, software, etc. for better performance and environments.

My question - while testing what will be my new production server I came accross several "weird" glitches... Ac ouple of the things were just necessary tweaks, but one problem required altering permissions on the execution of an extended stored procedure in the Master Database. These permissions solved the problem, however were not necessary for the current production environment...

The old (current production server) environment uses NT and uses SP2 with SQL Server 7.0. The "new" environment uses SP3 on Win2K Server. Can either of these differences be the reason for this problem? Or is there more troubleshooting to be done?

I'm a bit nervous, because we are supposed to go live with this new environment in less than a week! Please help! BeckahC

 

[DrewConn]

Can you give me some specifics on what errors you are getting?

 

[BeckahC]

Right now one user testing the system is getting:

error source: microsoft ole db provider for sql server error description: execute permission denied on object 'stored procedure name', database 'database name', owner 'dbo'.


The problem is that none of the permissions have changed! BeckahC

 

[DrewConn]

You have probably checked this already but make sure the permissions are set on the specific DB for execution on that object. If the user has been assigned a role verify the role setting are allowing sp execution.

Hope it helps.

 

[BeckahC]

Well... the roles and users are identical on the one that works and the one throwing the errors... the stored procedure can be run by anyone in the appropriate roles and everytime I grant access to one extended stored procedure, it won't let them into another used by the same stored procedure as the previous one... One of the errors, received, after giving the user's role access to the extended stored procedure actually was denying access to the stored procedure that uses that extended procedure...which she had obviously been able to access previously in order to get the previous error.

I am going to delete the entire database that I was trying to get to work (not the production) and then check all the settings between the production server and the new server and see if anything is different. Then I will have to start all over again.

I appreciate your help.

Thank you BeckahC