asked
database role
Im trying to create single Role to do the below. Can some one help me if possible. I need a role to do the below. Reading tables,Creating tables,Dropping tables,Reading views,Executing Stored Procedures,Executing Functions But also want this role to prohibit: Creating, altering or dropping stored procedures and Creating, altering or dropping functions Any thoughts. How can i acheive this?
Is this for a specific database, or do you want it to carry through across all databases on a server including those that don't exist yet?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
I've done something very similar and blogged the code here:
https://davidwimbush.wordpress.com/2016/01/06/developer-database-permissions/. You can easily tweak which permissions are granted in your environment. There's sample code to apply it to one database or a list of them.
I would add the role to the following database roles: db_datareader db_ddl_admin GRANT EXECUTE to role DENY CREATE PROCEDURE DENY ALTER PROCEDURE DENY DROP PROCEDURE DENY CREATE FUNCTION DENY ALTER FUNCTION DENY DROP FUNCTION I Think that would do the trick.
20 People are following this question.
