CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Johan Holtby
Johan Holtby

Reputation: 573

Can I limit a SQL user to only stored procedures?

I have a database and depending on the user I want them to see different things using stored procedures. I don't want them to be able to do direct SQL commands like select. Is this possible?

Upvotes: 2

Views: 4379

Answers (2)

Joe C
Joe C

Reputation: 3993

In the past I have had to give access to a handful of procedures and I would give the login public rights and no database mapping. This will let them log in but not do anything. Then I gave select rights to each stored procedure. I am pretty sure I didnt have to do anything else.

If you have a lot of stored procedures then this article will help you to create a specific role / schema for this.

http://www.patrickkeisler.com/2012/10/grant-execute-permission-on-all-stored.html

Upvotes: 4

Alec.
Alec.

Reputation: 5525

You need to remove the user from all groups then just use GRANT on the stored procedures you wish them to have.

-- Syntax for SQL Server and Azure SQL Database  

-- Simplified syntax for GRANT  
GRANT { ALL [ PRIVILEGES ] }  
      | permission [ ( column [ ,...n ] ) ] [ ,...n ]  
      [ ON [ class :: ] securable ] TO principal [ ,...n ]   
      [ WITH GRANT OPTION ] [ AS principal ]

Upvotes: 1

Related Questions