How to create login that only has permission execute stored procedures

Question

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

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

ss-457805 SSCertifiable Points: 5872 More actions · Answer 1

/* CREATE A NEW ROLE */

CREATE ROLE Rolename

/* GRANT EXECUTE TO THE ROLE */

GRANT EXECUTE TO Rolename

David Sorauer SSCrazy Points: 2874 More actions · Answer 2

You can do the same with a user. Just create a new login in SQL Server and GRANT execute on the desired stored procedures.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Combine Sarvesh's and David's suggestions:

1. create a login: create login domain\login from windows

2. create a user in the specific database: create user user for login domain\login

3. create a database role: create role rolename

4. grant execute to the role: grant execute to rolename

5. add the user as a role member: sp_addrolemember rolename, user

Greg

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

one additional thing that might make it easy (on top of the last post)

create a schema for your procs

set the schema as the new users default schema and grant permissions on the schema

MVDBA

cgr-1102585 SSC Journeyman Points: 83 More actions · Answer 5

Thanks, everyone, for your quick response.

Can I define the public server role to only GRANT permission to a user's default database or its schema objects?

One concern is that, once a user is logged in, they could view/modify/create other server objects such as the system databases.

cgr-1102585 SSC Journeyman Points: 83 More actions · Answer 6

SSC Journeyman

Points: 83

July 9, 2009 at 11:12 am

Thank you everyone.