CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Girish K G
Girish K G

Reputation: 417

How can i set a sql server user to execute stored procredures in readonly mode

I want to set a readony permission to a database user. This user should have permission to execute stored procedures But this user should not have permission to update or inser or alter anything in database through these stored procedures or through any methord can anyone help please

Upvotes: 2

Views: 5065

Answers (4)

Diego
Diego

Reputation: 36176

give execute permission on the proc and make sure the user doesn't have write access to the tables used by the proc. Check if he is not member of the db_datawriter roled.

If you want to be 100% sure, add the user to the db_denydatawriter role

Upvotes: 0

gbn
gbn

Reputation: 432639

Simply don't grant permission on the stored procs that change data.

Otherwise, you could use triggers on the tables to check, but that's a bit silly compared to using permissions correctly

Note: ownership chaining means that permissions on tables won't be checked, even DENY, so this won't work.

Upvotes: 4

Ray
Ray

Reputation: 46595

You'll need to set the permissions on a procedure by procedure basis. Give them permission to stored procedures that select, and don't give them permission to the stored procedures that do updates or inserts.The best way is to setup roles, and add the users to the roles.

This is pretty flexible and you can use it to restrict exactly what uses can do. For example, it may actually be okay for them to update a table as long as they do it through a certain stored procedure. To do this you can deny them all permission to that table, but grant them permission to execute the stored proc.

Upvotes: 0

Neil Knight
Neil Knight

Reputation: 48587

Yes. Set the EXECUTE permission for that user, then they'll only be able to execute the Stored Procedures in your database.

GRANT EXECUTE ON dbo.storedprocedurename TO SQLUSERNAME;

Upvotes: 2

Related Questions