Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
2  
when you say no access to the master database, are you making them a user in master to simply deny the data reader/writer? I think a much more secure option is not to make them a user in master. There is a distinction between logins and users in sql server, make sure you understand it. –  Nick Kavadias Aug 24 '09 at 17:31
    
@Nick - I agree, however, I found from my testing, unless I explicitly set the db_denyreader/writer on master, the master database is still available to the user I am creating. Thoughts? –  Gavin Miller Aug 24 '09 at 17:52

2 Answers 2

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.

share|improve this answer
    
Excellent, I was looking for a way to do this in SQL2000 as in 2005 the GRANT EXECUTE ON ... is a really nice feature to have! –  Dan Mar 4 '10 at 17:09

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.