login/user cannot execute or see stored procedures

Question

Post reply

login/user cannot execute or see stored procedures

Tobar

SSCarpal Tunnel

Points: 4876
More actions
May 6, 2014 at 7:47 am

#285379

We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.
<><
Livin' down on the cube farm. Left, left, then a right.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

You could grant execute on the schema that the procs are in.

GRANT EXECUTE ON SCHEMA :: schemaName TO username;

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 2

So the user is part of the "schemas" db_writers and db_reader. Should I just grant execute to one of those? (Sorry, even after reading up on SQL Server schemas I am still a little confused).

<><
Livin' down on the cube farm. Left, left, then a right.

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

the procedures are in a schema.(http://technet.microsoft.com/en-us/library/ms190387(v=sql.105).aspx) If you do not specify schema, all your objects (tables, views, procs, triggers, functions, etc) will be in the default schema of the user that created them. You would need to grant execute on what ever schema all your procedures are created in if you wanted the user to be able to execute all procs now and going forward.

you can identify which schema your procs are in with the following query.

SELECT b.name,a.name

FROM sys.procedures a

INNER JOIN sys.schemas b

ON a.schema_id = b.schema_id

most likely your procs will be in the dbo schema.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 4

You can think of a schema as a logical container of objects. When you grant permissions on a schema, you are granting those permissions on all of the objects currently in the schema, as well as any objects that are added later.

If the application is at least advanced enough that all of the data access is by the stored procedures, you could actually revoke the membership in db_datareader, and db_datawriter, and just leave the grant execute on the dbo schema.

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 5

Thanks. You both helped my understanding of this subject, as well as providing me with the resolution.

<><
Livin' down on the cube farm. Left, left, then a right.