Secure user setup in Sql Server 2000

Question

I'm looking to setup a user account in Sql Server 2000 in the most secure manner. The criteria is as follows:

• No access to the master database (db_denydatareader & db_denydatawriter on master)
• DB Read Permission (db_datareader)
• DB Write Permission (db_datawriter)
• DB Execute Permission for sprocs (???)

To me this seems like a pretty straight forward setup and that it ought to be a standard scheme. Running through a couple articles show that it isn't and that granting sproc execute permissions is less than easy.

Is there an easier way to grant sproc execute permission? Is there a catch all scheme that provides secure database access? Am I tracking on the right path?

Answer 1

There is no built-in database role for stored procedure execution rights in SQL Server 2000. However, the first article you quoted actually contains everything you need to completely solve your problem. However, I'll try to spell it out a little more clearly in a step-by-step process (that you will only need to follow once to create the role, and then re-run the last step only whenever you create a new procedure). In this procedure, I am going to use the name my_database for the database you are wanting to configure in this way:

1. Create the stored procedure sp_grantexecute (linked from the first article):

   use master
   go
   create procedure sp_grantexec(@user sysname,@pattern sysname = NULL,@debug int = 0)
   as
   set nocount on
   declare @ret int
   declare @sql nvarchar(4000)
   declare @db  sysname ; set @db = DB_NAME()
   declare @u   sysname ; set @u = QUOTENAME(@user)
   
   set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
             QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' + 
             'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
   
   if @pattern is not null
   set @sql = @sql + N' AND ROUTINE_NAME LIKE ''' + @pattern + ''''
   
   if @debug = 1 print @sql
   else
   exec @ret = master.dbo.xp_execresultset @sql,@db
   
   If @ret <> 0
   begin
      raiserror('Error executing command %s',16,1,@sql)
      return -1
   end
   

2. Create a custom role called db_executor (repeat this step for each database where you want this role configured)

   USE my_database
   GO
   EXEC sp_add_role 'db_executor'
   

3. Grant execute rights for all existing procedures to the new role (run just this step every time you create a new procedure) using the sp_grantexecute procedure you added previously:

   USE my_database
   GO
   EXEC master.dbo.sp_grantexecute 'db_executor'
   

Now you can simply assign the new role (db_executor) in addition to the two other roles (db_datareader and db_datawriter) to users that should have the access that you are looking for.

Answer 2

In addition to Jessica's answer, you can obviously set up a SQL Agent job to run sp_grantexec every minute or something like that. If you are on SQL Server 2005, I have had success with database-level schema triggers to grant the EXEC permission to db_executor whenever a stored procedure/function is created.

Absent of other requirements, usually you don't need to worry about master read/write but instead execute on certain extended stored procedures on 2000 (most system tables/views limited by role of user running the query (Granted logins do not have associated users).

http://vyaskn.tripod.com/sql_server_security_best_practices.htm is a good resource for locking down a SQL 2000 instance. Remember always to test on a non-production machine first.

MBSA sometimes can point out basic issues in SQL as well.