Execute permission was denied

Question

Execute permission was denied

Chris Lee-152270

SSCommitted

Points: 1809
More actions
January 16, 2008 at 12:55 pm

#185741

Hi,
I just restored a SQL 2000 to 2005 database (Windows 2003 server, SQL 2005 SP 2), tried to access the web and got the error:
[Macromedia][SQLServer JDBC Driver][SQLServer]The EXECUTE permission was denied on the object 'proc', database 'database', schema 'dbo'.
The procs were granted the execute to a 'role'. I tried to add the 'Role' to 'user' but got an error message:
Login name must be specified. (SqlManagerUI)
Even though the Login Name was gray out. Any hint or suggestion? Thanks.
Chris

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

When you created the user, did you create it with the their SID?

If not try recreating the logins with the SID. Or drop the user from the DB and the SQL Server and recreate it and remap it to the DB that you just imported

-Roy

Chris Lee-152270 SSCommitted Points: 1809 More actions · Answer 2

Hi Roy,

Thanks for the help. Users were creates during database was restored. I checked SID in sysusers; it was populated. Could I just update SID to NULL?

Chris

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Do not update any System tables. What you can do is drop the user and the login and recreate them again and add it to the Role that is there.

-Roy

Stuart Folk SSCrazy Points: 2424 More actions · Answer 4

I assume you are talking about a SQL logon?

If you are questioning the relationship of the logon in the sysusers entry in the database to a SQL logon read up on the stored procedure sp_change_users_login in BOL.

Chris Lee-152270 SSCommitted Points: 1809 More actions · Answer 5

Hi Stuart,

We just migrated from SQL 2000 to 2005 (Windows server 2003/Ent SP 2). SQL 2000 database was backed up and restored (attached) as SQL 2005 database. Most of procedures grant execute to a web role. This web role associates with a database user.

Tried to bring up the UI (ColdFusion 7) and encountered the error. I did find the database user SIDs (thanks to Roy's sugguestion) were wrong. I still see the same error after droping and re-creating the user.

Is it true that there is different way to grant execute in SQL 2005? Many thanks.

Chris

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 6

Well if all the procs belong to the same schema then you can grant execute to the user on the particular scehma and then the user will have execute permission to all the procs in that scehma. Also in future if any new procs are added to that scehma then the user will by default ahve the execute permission and you need to add it a fresh.

Koudjo Nofodji SSC Journeyman Points: 98 More actions · Answer 7

Do the following:

1- delete all user IDs that came from the restore

2- Look for sp_help_revlogin and run it on the source server

3- Copy the output and run it on the target server

4- Run sp_change_users_Login 'report' and it will give you the IDs that needs to be fixed

5- Run sp_change_users_Login 'auto_fix', 'xxxxxxx' with xxxxxxx being the login to be fixed

Koudjo Nofodji