Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    13

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, back it up regularly, and don't put anything in master

    Why are you doing this?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I suggest you to use this form "SELECT * FROM master.dbo.table"
    and put your proc somewhere else.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  4. #4
    Join Date
    Jan 2007
    Posts
    13
    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.

  5. #5
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I'm pretty sure you can. Did you try it?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  6. #6
    Join Date
    Jan 2007
    Posts
    13
    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'

  7. #7
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    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]
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •