Stored Procs and db_owner

[db55]

I have some users that I need to run stored procedures, but they can't
seem to run them unless they are in the db_owner role of the database.

How do I give them access to run the stored procs without giving them
the complete rights of the db_owner role?

Thanks in advance.

[Erland Sommarskog]

db55 (ch****@gmail.com) writes:

I have some users that I need to run stored procedures, but they can't
seem to run them unless they are in the db_owner role of the database.

How do I give them access to run the stored procs without giving them
the complete rights of the db_owner role?

GRANT EXECUTE ON proc TO user

Or rather than granting permissions directly to users, it's probably
better than adding them roles, and then grant access to the role instead.

But note that just because you grant them access to run the procedures,
that does not mean that they can run the procedures successfully. They
will get SELECT, INSERT, DELETE and UPDATE permissions to tables referenced
by the stored procedures, if the tables and procedures have the same
owner. However, this so-called ownership chaining does not apply to
other statements such as CREATE/DROP TABLE (temp tables are OK) or
TRUNCATE TABLE.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp