Results 1 to 7 of 7

Thread: MSSQL Login for Stored Procedure

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Thumbs up MSSQL Login for Stored Procedure

    Hello,
    I am having a challenge creating a MSSQL Database Login that can ONLY create, delete, execute some few Stored Procedures Only. I dont want that user to be able to view table or perform any other SQL query.
    There are 13 stored procedures under the database named "OFFICEDB" and i want the login to be created to only have access to create, delete, execute or alter just 4 selected stored procedures only.
    Kindly guide me on this.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: MSSQL Login for Stored Procedure

    How exactly would you enable a login to create procedures that already exist? If that login has permission to delete those procedures then they no longer exist, so anything they create will be different procedures.

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: MSSQL Login for Stored Procedure

    I would just grant execute on the procedures to the user you want.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Re: MSSQL Login for Stored Procedure

    @jmcilhinney and @ GaryMazzone
    I just want to create a MSSQL Database Login which will ONLY have access to access stored procedures on the database. It should not be able to view my tables or execute any other thing apart from the Stored Procedure.
    I am asked to create the login credentials and send to the guy handling the stored procedure, but i dont want to give him a login where he can use to do anything else or tamper with anything else.

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: MSSQL Login for Stored Procedure

    You create the SQL login. You grant connect to the database you want then only grant execute on the stored procedure you want that login to be able to execute..... If it is all SPs in the DB you can grant execute on schema. Or you grant execute on a storeProc itself
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: MSSQL Login for Stored Procedure

    You can create the login and add it to the dbExecute role, which should have access to the sprocs... nothing more... That should be sufficient. If you want this user to have access to one and only one sproc, then you'll need to GRANT EXECUTE to that one sproc explicitly.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,414

    Re: MSSQL Login for Stored Procedure

    Quick question from the sidelines: In which context is a SP executed?
    Let's stay with the requirement:
    User shall have GRANT's (only those) to execute, create, change and delete a (specific) SP

    Let's go with the CREATE-Grant:
    That user creates a SP, which contains "DROP TABLE MyTable......."

    Is the SP executed within the User-Context?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width