Monday, March 19, 2012

Execute user stored procedure in master

I need some clarification regarding the security inside of the master db.

I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.

Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?

Thanks in advance.Yeah, back it up regularly, and don't put anything in master

Why are you doing this?|||I suggest you to use this form "SELECT * FROM master.dbo.table"
and put your proc somewhere else.|||The stored procedure was originally thrown in the master db as a way of notifying admins when jobs fail. It's a stored procedure that uses certain extended stored procedures in the master db to send an e-mail message. (No we can't use SQL Mail). Could I put the stored procedure in another user db and allow the id execute permission?

Thanks again.|||I'm pretty sure you can. Did you try it?|||Thanks ortho. I copied the stored procedure from master to a user db and gave the user execute permission on it. I got the following error

EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'|||maybe dbo does not have access to master
try using the schema for the user you gave access to master OR the user name.

Did you gave execution access to dbo?

SELECT * FROM [server].[database].[user].[table]

No comments:

Post a Comment