-
Apr 23rd, 2021, 05:55 AM
#1
Thread Starter
Member
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.
-
Apr 23rd, 2021, 08:00 AM
#2
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.
-
Apr 23rd, 2021, 08:04 AM
#3
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
-
Apr 23rd, 2021, 08:36 AM
#4
Thread Starter
Member
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.
-
Apr 23rd, 2021, 09:21 AM
#5
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
-
Apr 23rd, 2021, 11:21 AM
#6
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
-
Apr 26th, 2021, 02:07 AM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|