making stored procs read only for developers(SQL 2000)

Question

Post reply

making stored procs read only for developers(SQL 2000)

David Severson

Grasshopper

Points: 19
More actions
September 11, 2006 at 10:01 am

#97718

I am in a situation where I need to make our production database stored-procs readable by developers but not (remove, update, delete,etc). I can't seem to find a mechanism that allows me to do that. Can anyone help me?
- dave severson

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 1

I've only tried this against SQL 2005, but it works fine there... maybe it'll work on SQL 2000 too?

GRANT VIEW DEFINITION ON [dbo].[YourSProc] TO [user_or_group]

This allows the user or group to see the procedure definition, but not to change it. If you want them to be able to execute it too, also add:

GRANT EXECUTE ON [dbo].[YourSProc] TO [user_or_group]

This assumes they are logging on without higher privileges like database owner or sysadmin. Generally developers shouldn't need much authority on a production system (as a developer, it hurts to say that , so give them minimum rights to start with and add rights, such as above, as needed.

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 2

p.s. If they've already got high permissions, you can selectively turn off permission to change a stored procedure like this:

DENY

ALTER ON [dbo].[YourSProc] TO [user_or_group]

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

In SQL 2000 you can deny CREATE PROCEDURE permissions to users in the ddladmin role, but unfortunately that doesn't prevent ALTER PROCEDURE or DROP PROCEDURE.

You could script all your procedures and put them under source control, then periodically check the scripts against what is on the server. There are several schema-compare products on the market that would make this easy, but it is a manual process and if you find changes it won't tell you who made them.

You can't prevent changes, but if you're really determined to catch changes (and find out whodunnit) you can look for changes in the procedure definitions. Save the proc list and checksums from the script below to a table in a database they DON'T have rights to (they don't have the sa password or sysadmin rights, do they?) and schedule a job to run periodically to look for changes. If there is a change, learn how to read the transaction logs (i.e. buy a tool that can do this) to find out who the culprit was. This script collects checksums for non-system procs in all databases, if you're only interested in one database you can simplify it.

SET

QUOTED_IDENTIFIER OFF

create

table #procs (

ProcName nvarchar(500),

colid smallint,

hashcode int)

EXEC

sp_msforeachdb "

insert into #procs

SELECT quotename('?') + '.' + quotename(su.name) + '.' + quotename(so.name), colid, checksum(text)

FROM [?].dbo.sysobjects so

INNER JOIN [?].dbo.sysusers su ON su.uid = so.uid

INNER JOIN [?].dbo.syscomments sc ON sc.id = so.id

WHERE so.xtype = 'P' AND so.status > 0"

If you're extremely paranoid and don't even want the developers to know you're doing this, put this in a DTS package that is scheduled to run on your own PC, and put the reference table in another place (SQL Express instance on your PC, Access database, etc.)

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Too bad GRANT VIEW DEFINITION isn't available in SQL2000. If you only want people to be able to see stored procedure code, grant READ permission on sysobjects and don't grant any other permissions to them or make them members of db_ddladmin.

Greg

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 5

I personally like the idea of having all the procedures checked into source control and then just not allowing the developers access to the procedures on the server as they no longer need it (they can access the source via the source control).