Restricting access to T-SQL within Enterprise Mgr

Question

Restricting access to T-SQL within Enterprise Mgr

bfarley

SSC Veteran

Points: 291
More actions
July 30, 2008 at 5:56 am

#72772

There are some users who use a SQL Server 2000 database strictly for data export using a DTS package. Data privileges are not an issue, but restricting their ability to view (and potentially copy) custom T-SQL code is a concern.
I'd like to lock them down to prevent them from viewing code (stored procedures, user-defined functions, triggers, etc.) while continuing to allow data access.
How can that restriction be set for a group/role in Enterprise Mgr?
Thanks in advance!

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

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

Brian Munier SSCrazy Points: 2875 More actions · Answer 1

bfarley (7/30/2008)
There are some users who use a SQL Server 2000 database strictly for data export using a DTS package. Data privileges are not an issue, but restricting their ability to view (and potentially copy) custom T-SQL code is a concern.
I'd like to lock them down to prevent them from viewing code (stored procedures, user-defined functions, triggers, etc.) while continuing to allow data access.
How can that restriction be set for a group/role in Enterprise Mgr?
Thanks in advance!

Stored procedures and possibly views and triggers can be created with an encryption option which would protect that code and therefore help hide your code.

Make sure to keep an uncompiled copy in a safe place.

Brian

bfarley SSC Veteran Points: 291 More actions · Answer 2

Appreciate your input, Brian.

It sounds as though the options may be limited to (1) encrypting as you described, or (2) the user accomplishing the tasks without Enterprise Mgr as an interface.

Does that sound about right?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Just be aware that in SQL 2000 decrypting encrypted stored procedures was a fairly trivial operation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brian Munier SSCrazy Points: 2875 More actions · Answer 4

bfarley (7/30/2008)
Appreciate your input, Brian.
It sounds as though the options may be limited to (1) encrypting as you described, or (2) the user accomplishing the tasks without Enterprise Mgr as an interface.
Does that sound about right?

There is a TSQL stored procedure:

sp_helptext [ @objname = ] 'name'

which displays the code unless there is an encryption option set.

So your option to deny Enterprise Mgr won't solve the problem.

As Gail said there may be easy ways to avoid the encryption in SQL 2000, and if you are that concerned about people getting the code, a re-design or move to SQL 2005 may be in order.