Post reply

EXEC store proc in master DB

  • November 16, 2008 at 12:36 pm

    #124854

    I created a store proc in master dataabse with prefix "sp_" but when a DB Role which is not an admin tries running that proc it gives permission error. For the role i have granted EXEC permissions for that store proc but still i get permissions error, is there anything else i have to do with regard to permission.

  • November 16, 2008 at 2:45 pm

    #899102

    Depends if the sp needs to have system admin rights which sounds like it does.

    If the user needs to be able to run this and it is something like alter login then the rights need to be systems admin for this user. If you don't want to give these rights out your have to look into certificates.

  • November 16, 2008 at 8:13 pm

    #899133

    Depends. What does the proc do?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 16, 2008 at 8:44 pm

    #899141

    Are you trying to get users to run it from another DB?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • November 17, 2008 at 6:24 am

    #899291

    I am trying to get users to run it from any DB in the instance to check the Lock Details.

    Also how can i test that proc by running as a regular user because my windows login has a sysadmin previlages.

  • November 17, 2008 at 7:17 am

    #899320

    After you create the script, run this command from within master:

    exec sys.sp_MS_marksystemobject 'sp_YOUR_SCRIPT_HERE'

    which allows you to run it from any database.

    Gaby
    ________________________________________________________________
    "In theory, theory and practice are the same. In practice, they are not."
    - Albert Einstein

  • November 17, 2008 at 7:48 am

    #899340

    I tried that but still didnt work.

    If am doing os for making it sys proc, can i delete it later, does it impact anything by delete.

  • November 18, 2008 at 7:25 am

    #899911

    I am just looking for a way where a regular user can execute sp_Lock system store procedure.

  • November 18, 2008 at 7:32 am

    #899918

    Need to look into certificates this way you can allow the user to do this.

  • November 18, 2008 at 7:32 am

    #899919

    Does the average user get what sp_lock is telling them, why don't you just do an alert with email to the user instead that there is a lock, also what are they going to do with this information.

  • November 18, 2008 at 7:49 am

    #899931

    what do yo mean by alert through email, how is it possible. my developers whats to look at locks and who is doing what as that group will be kicking jobs with huge amounts of data flowing in and they want make sure their jobs run OK when the resources are free.

  • November 18, 2008 at 8:14 am

    #899952

    Developers shouldn't be on a production database, if it is a test database then i would give them sa rights...after all it is a development system.

    If you don't want to do this then your need to use certificates.

    Look at this.

    What it does it prevents you from giving sa rights to the user - so your create a sp with say

    sp_lock in it and then your assign the certificate and then grant this certifiate to your user.

    So basically the user can run your sp but thats all they can do on the server.

    It gets a little complex with certificates but i have used it on my server.

    http://blogs.msdn.com/lcris/archive/2005/06/15/sql-server-2005-procedure-signing-demo.aspx.

  • November 18, 2008 at 8:31 am

    #899964

    Mike Levan (11/17/2008)

    I am trying to get users to run it from any DB in the instance to check the Lock Details.

    Also how can i test that proc by running as a regular user because my windows login has a sysadmin previlages.

    I create a 2nd Windows login for myself with no special rights, then I use that for testing as if I was a regular user.

    Mike Levan (11/16/2008)

    I created a store proc in master dataabse with prefix "sp_" but when a DB Role which is not an admin tries running that proc it gives permission error. For the role i have granted EXEC permissions for that store proc but still i get permissions error, is there anything else i have to do with regard to permission.

    Isn't it better to us a different prefix than 'sp_' when creating your own stored procs ?? Then they won't get confused with system stored procs during upgrades or migrations.

  • November 18, 2008 at 8:48 am

    #899981

    Isn't it better to us a different prefix than 'sp_' when creating your own stored procs ?? Then they won't get confused with system stored procs during upgrades or migrations.

    But still i dont want to create my proc on each n every database. I want to create in master and run it in all db's

  • November 18, 2008 at 8:56 am

    #899991

    You wouldn't have to do this...You could have it in master no need to do in every database.

    If you have a system database ..i.e SYSADMIN which is where your putting your custom sp etc..then u fire the stored procedure from here for the users it will look at all your locks

    not just in the SYSADMIN one

    You can do it all from master - but who puts custom sp in master ...that should be a no no.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply