May 18, 2009 at 10:03 am
I'm unable to grand OLE execute permissions to users.
I've enabled Ole Automation Procedures via:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
and now I'm trying to grant access to user "jondoe" (a database login) via:
use master
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO jondoe
but I keep getting the error "Cannot find the user 'jondoe', because it does not exist or you do not have permission."
I'm running this as the SA user, so I should have permission, and the user definitely exists since I can login separately as them. What would be causing this error, and how can I fix it?
Regards,
Chris
May 19, 2009 at 1:01 am
Does the following code work?
USE master
GO
CREATE USER jondoe FOR LOGIN jondoe
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO jondoe
GO
--Ramesh
May 19, 2009 at 7:15 am
Close. I found there were two problems. I had recently done a restore, so the database login and user were disconnected (and of course SQLServer doesn't tell you this).
I fixed that by running:
exec sp_adduser @loginame = 'jondoe', @name_in_db = 'jondoe'
This allowed the GRANTs to work. However, this still didn't allow the user to execute OLE procedures. I later found out that the user has to belong to the sysadmin role in order to use these, which is too big a security risk, so I'm abandoning this route entirely.
May 19, 2009 at 7:21 am
Though allowing "OLE Automation Procedures" is a security threat, but it does not require sysadmin privileges in order to execute those procedures (though the documentation says it requires sysadmin privileges but it works just by giving execute permissions on the sp_OA* procedures)
--Ramesh
May 19, 2009 at 7:29 am
"(though the documentation says it requires sysadmin privileges but it works just by giving execute permissions on the sp_OA* procedures)"
The documentation seems to be correct in my experience. I couldn't get it to work by simply granting execute permission. The user had to be explicitly added to sysadmin before it would work. Is there some work-around for this you're using?
May 19, 2009 at 7:36 am
I am sure that it does not require sysadmin privileges because we have been using OLE objects to send mails in all of our legacy applications. All we have done is created a login, granted db_owner database role to one of the application databases and then granted execute permission on the procedures sp_oa*.
--Ramesh
June 11, 2009 at 9:45 pm
I am a new learner of SQL server.
How to remove the execute permissions from those users which are not DBA users?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply