Sql-server – the proper way to grant stored proc execute permissions for a SQL Server user

permissionssql server

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

Best Answer

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
Related Topic