Users Cannot Run Stored Procedures 1

[DomDom3]

Hi there,

I've put a database together which all works fine on my PC (I'm the db owner).

The problem is that other users cannot use the databse as they cannot run the stored procedures and so combo boxes etc. won't work.

Initially they had permissions of datareader and datawriter, I then added db_owner role, but there's still no change.

Can anyone tell me which eprmissions I shoudl use please?

Thanks

 

[onpnt]

make sure the permissions on the database have EXEC rights


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[DomDom3]

Thanks, does this have to be done for each SP or is there a universal method of setting exec rights?

 

[Catadmin]

You have to do it one by one. The only universal method is to use a cursor. Here's one I use:

DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT so.name as FunctionName
FROM sysobjects so 
WHERE so.type = N'FN'

OPEN c 

SET @Stmt = N'--'

FETCH FROM c INTO @FunctionName
WHILE (@@FETCH_STATUS = 0) 
BEGIN 

    -- statement to grant execute permissions
   SET @Stmt = N'GRANT EXECUTE ON dbo.' + @FunctionName  + ' TO [Domain\UserGroup]'
	Print @Stmt
   EXEC sp_executesql @Stmt 
   
   FETCH NEXT FROM c INTO @FunctionName
END 

CLOSE c 
DEALLOCATE c

GO

Hope it helps.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[onpnt]

sorry, long drive to work...yes they have to be set one by one

Catadmin took care of you though


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[mrdenny]

In SQL 2000 use the script that Catadmin provieded. In SQL 2005 you can grant exec rights to the schema that owns the procedures and the user will pickup exec rights to all the procedures, functions, etc within the schema.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com