CODEWITHSUNDEEP
sql-serverstored-procedurespermissions
Craig
Craig

Reputation: 4239

SQL user can only run proc, but that proc can do anything

In SQL Server 2005, I want a user, called LimitedUser, to only be able to run one proc: GRANT EXEC ON [usp_RunETL] TO [LimitedUser]

However, that proc needs to be able to do everything -- UPDATE, DELETE, INSERT, EXEC.. everything. How do I do that without having to give all those permissions to LimitedUser?

Upvotes: 2

Views: 247

Answers (2)

Ryan
Ryan

Reputation: 8005

You only need to GRANT EXEC to the limited user. As long as the user has permissions to the stored procedure, it ignores/overrides permissions for operations inside of the procedure.

I would be very careful doing this though, this sounds like a big potential security hole. Typically you would use multiple procedures to have the flexibility to adjust permissions as necessary. I would recommend to assign the permissions to the procedure, or to the underlying tables and views, rather than decide that there will be only one super procedure.

Upvotes: 0

DOK
DOK

Reputation: 32851

LimitedUser only needs permission to run the sproc. They don't need any other permissions.

Upvotes: 3

Related Questions