role : Complete access minus create tables

kamur used Ask the Experts™

In one of my dev environments, I need to restrict access to all developers. Being a dev area, currently all the developers have complete access/ sysadmins. Now, we need to still retain all the access that the developer has, but just do not allow any developers to create new tables/alter existing tables. Delete is fine. (Create other objects , sps, triggers, views etc are fine). They stilll should have all other complete accesses including sql agents, execute, etc.

How can I create a new role that's almost similar to sysadmin level but minus the CREATE tables acess.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr Database Engineer
You cannot, once a sysadmin role is granted, it has all the permission to so anything. You cannot grant a specific user an "sa" role then limit it to a certain specific exclusions.

What you can do is list down what "all other complete accesses including sql agents, execute, etc." they need. Crate a role, add all the permission/access to that role and deny create/modify table permission. But do nt grant "sa" role.


Thanks for the solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial