1

I have just moved a SQL Server 2000 DB over to SQL Server 2005 Express (which will be upgraded soon) and in the process I am cleaning up some bad habits from the previous owner.

For one, the old web app used the "sa" account to access the DB. I've now created a new Login and mapped that to a User in this specific database with db_datawriter and db_datareader roles. Login works fine... but when the app attempts to execute any stored proc, I get an error about the proc not being found. It's a permissions issue... Connecting to the server via Management Studio using the new credentials shows me that none of the app's stored procs are even visible, so the error makes sense.

But... there are two or three hundred stored procs here. How do I grant this user access to execute any of the stored procs without having to modify permissions on every one? For now I went and added the "db_owner" role for this user. But that seems like overkill... ?

user21146
  • 367
  • 1
  • 5
  • 19

2 Answers2

5

Yep, that's overkill. The easiest way to do it is to grant the user rights to execute all procedures in the schema (or better yet grant a role this right).

First create a new role. Call it YourAppRole (or whatever, the name doesn't really matter). Make your user account a member of the role. Grant the role execute rights to the dbo schema (or whatever schema the procedures are in). This can be done in the UI, or via code.

GRANT EXEC ON SCHEMA::dbo TO YourAppRole

Or you can write a T/SQL script to go though all the procedures and grant then the right. Technically this is a more secure option.

DECLARE @proc sysname
DECLARE @cmd varchar(8000)

DECLARE cur CURSOR FOR select '[' + schema_name(schema_id) + '].[' + name + ']' from sys.procedures
OPEN cur
FETCH next from cur into @proc
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @cmd = 'GRANT EXEC ON ' + @proc + ' TO YourAppRole'
     EXEC (@cmd)

     FETCH next from cur into @proc
END
CLOSE cur
DEALLOCATE cur
mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • 1
    Hi Denny... you should use QUOTENAME of course, in case there are bad characters in the proc names. – Rob Farley Oct 21 '09 at 03:49
  • Good point. Added the square brackets. – mrdenny Oct 21 '09 at 07:46
  • Thanks. I think I should go with the first option... otherwise every time I add a new stored proc I have to explicitly set permissions, right? – user21146 Oct 21 '09 at 18:11
  • Correct if you grant rights to each proc, then yes as you add new procedures you would need to grant rights to each procedure. If you go with the first option you don't have to do this. – mrdenny Oct 22 '09 at 05:36
  • If you want the user to actually be able to see the code you can also add VIEW DEFINITION permission with a comma. (That won't allow user to alter that's a whole other permission) – Gabriel Guimarães Nov 23 '10 at 15:12
0

If they are all in the same schema you can grant execute in it.

Something like...

grant user execute on schema::dbo

Then get rid of the other roles you added,test, and see if it needs permissions on base tables

Sam
  • 2,020
  • 1
  • 16
  • 22