October 21, 2009 at 12:41 pm
I enabled OLE Automation using ==> http://msdn.microsoft.com/en-us/library/ms191188.aspx
But when a user is trying to access it it is giving the error
Msg 229, Level 14, State 5, Line 1
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
So i tried granting the privilege from master
GRANT EXECUTE ON [sys].[sp_OACreate] TO PUBLIC;
That complains the user 'MyUser' does not exist. In other words it expects the user to exist in the master database.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22777078.html
But if i will create the user in master then, the grant will go to the user in the master database. Not my regular
database user.
For now i have granted access to PUBLIC to get around this.
1. How do i grant access to a private user?
2. Is it safe to grant to public the OLE automation stuff? What are the risks involved?
Thanks
October 21, 2009 at 1:59 pm
I think you have to restart the server after turning it on.
October 21, 2009 at 9:04 pm
Granting permission to public for these sprocs is a potentially huge security problem just waiting to happen. The sp_OA sprocs allow a user to call the methods of the applications installed on the server. This could easily be exploited. This is the reason that they are disabled at installation time.
If there is a need to allow these sprocs to be used, only grant permission to those individuals who need them - NOT to public.
October 22, 2009 at 10:48 am
Hello Happycat
Thanks for the information. But the problem is i am not able to grant the privilege to that particular user. Are you aware of any work arounds?
Thanks
October 22, 2009 at 9:32 pm
Has the login been granted access to master ? This is separate from creating the login
October 23, 2009 at 11:09 am
I don't understand this. How do i grant access to master? Does it mean granting access to views under master? If so what views? All views? Sorry, if my question sounds trivial.
Thanks
October 23, 2009 at 12:00 pm
Once you have created the 'MYUSER' login, you then need to create that user in the database. Through the GUI, this can be accomplished by double-clicking your user under security and then going to User Mapping and putting a check in the databases for which the user will have access (master in this case).
Or VIA TSQL
Use Master
Go
CREATE LOGIN MyUser DEFAULT_DATABASE=[Master] --This is the part you have already done
CREATE USER MyUser FOR LOGIN MyUser --this needs to be done
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy