making a stored procedure read-only

Question

making a stored procedure read-only

macon

SSC Enthusiast

Points: 114
More actions
March 17, 2005 at 3:06 am

#85639

Does anybody know if you can make a sproc read-only?
I'm trying to creating a sproc programmatically and some of the content can be entered by a user. The intention of the sproc is read-only and I want to ensure the sproc cannot alter any data.
Any ideas?
tia,
macon

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Helen-84411 Hall of Fame Points: 3320 More actions · Answer 1

How do u want to create a stored procedure programmatically- using any front end application. Use of inline statemtements is preferred. Use front end validation so that they do not include DML statement.

Then use sp_executesql to execute them.

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

macon SSC Enthusiast Points: 114 More actions · Answer 2

Thanks Helen,

I am writing a "designer" as part of a larger product that allows users to specify varying amounts of SQL (sometimes just a where clause, sometimes whole SQL statements) that get compiled into a stored procedure for use later by the larger product.

For validation I'll just let SQL Server compile the resultant sproc and pass any SQL errors back to the user. I just want to make sure that any valid SQL they specify can't alter data.

I can do it by creating the sproc with an owner that has denyDataWrite but I thought there might be a way of securing the sproc without my having to create the sprocs with a different owner.

Thanks,

macon

Pancho Villa SSC Veteran Points: 222 More actions · Answer 3

You cant make read only a SP, but you can control who can edit all stored procedures trow permission. if your users arent in the sysadmin role or db_owner and dont have the "Create Stored Procedures" permission they can modify the stored procedures.

But i guess its a really bad idea to make a Stored Procedure that contains user input. That could be very dangerous.

Saludos.

Pancho villa