July 7, 2009 at 8:31 am
How do you create a Windows authorized SQL server login that only has permission to execute the stored procedures of a particular database?
July 7, 2009 at 8:39 am
/* CREATE A NEW ROLE */
CREATE ROLE Rolename
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO Rolename
July 7, 2009 at 8:47 am
You can do the same with a user. Just create a new login in SQL Server and GRANT execute on the desired stored procedures.
July 7, 2009 at 9:42 am
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
July 7, 2009 at 9:51 am
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
July 7, 2009 at 11:10 am
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.
July 9, 2009 at 11:12 am
Thank you everyone.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply