November 1, 2007 at 3:37 am
Hi
I am facing an issue as below:
1. One of SQL Login (Which is an job actually) is db owner in user database and db_datareader and db_datawriter in master.
When tries to login and execute some SP on user database, it gets an error like error like:
[ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_XXXXX(It is in exended SP)', database 'master', owner 'dbo'.].
Can any body tell me why it happens like this? As he is db_datareader and db_datawriter on master, he should have execute permission on all sp s of master.. When I made this account dbo of master.. the issue is fixed.
Please let me know what is the issue here?
Thanks in advance...
November 1, 2007 at 3:44 am
I suspect some stored procedures require special permissions, i.e. datareader/datawriter is not enough. Just to give you one example, to use sp_password to change someone else's password requires you to be at least in the System Security group.
November 1, 2007 at 4:03 am
sp_prepare appears to be SS2005, i.e. don't see it in SS2K. In any case, many SPs in master have execute permission to public. I found at least one (sp_remove_tempdb_file) which does not have this permission. And as I mentioned regarding sp_password, it too has execute permission to public but if you try to use it to change someone else's password you will get bounced.
From my experience, the only time I every requested from a DBA datareader for master was when I had to have read access to sysxlogins which is a table.
Thus look at the permissions of sp_prepare. In SS2K, you right click on the object and do All Tasks/Manage Permissions. Surely there is something equivalent in SS2005.
November 2, 2007 at 12:02 am
cpuld you please post us the name of the extended procedure that is returning in the error message.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply