CODEWITHSUNDEEP
sql-servert-sqlroles
Kearl
Kearl

Reputation: 57

Can I setup a user in SQL Server 2008 R2 that can only execute certain stored procedures?

I'm running sql server 2008 r2 & i'd like to setup a user that can only execute given stored procedures with no other read privileges. I know how to limit database access and read only, but am not sure how to limit everything except stored procedure access. Where is this setup? Thanks in advance!

Upvotes: 0

Views: 137

Answers (1)

M.Ali
M.Ali

Reputation: 69514

-- Create a login 
USE [master]
GO
CREATE LOGIN [Proc_User] WITH PASSWORD  = N'SoMePa$$wOrD'
GO




-- Create a user in Target database with that login
USE [Test_DB]
GO

-- Create Test procedure
CREATE PROC dbo.TEST_PRCO 
AS
SELECT 1
GO

-- Create test User
CREATE USER [Proc_User] FOR LOGIN [Proc_User];
GO

-- Grant EXECUTE permissions on that procedure 
GRANT EXECUTE ON dbo.TEST_PRCO TO [Proc_User];
GO

Now this user has only permission to execute this Stored Procedure and NOTHING ELSE PERIOD.

Upvotes: 3

Related Questions