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... ?

Improve this question

2 Answers 2

Reset to default
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
Improve this answer
5
  • 1
    Hi Denny... you should use QUOTENAME of course, in case there are bad characters in the proc names.
    – Rob Farley
    Oct 21, 2009 at 3:49
  • Good point. Added the square brackets.
    – mrdenny
    Oct 21, 2009 at 7: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, 2009 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, 2009 at 5: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, 2010 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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .