August 2nd, 2012, 10:08 AM
Assign DML, DDL permission to login but not alter login, backup-restore, drop dbs
Hi! I need to create a login that has access on more than 10 databases.
This login can perform any DML, DDL operations (i.e. Select, Insert, Delete and Create, Drop SQL Objects etc.).
I need to restrict this login to not take the backup, restore databases, create or alter logins. Alter his/her own logins (especially Database role membership).
I tried to achieve the same by assigning db_Datawriter and db_ddladmin role membership. But, after assigning these roles; this login is not able to
Select data from existing tables, alter tables from GUI, and Create new tables from GUI.
Hence, I assigned "db_Owner" role membership to this login. Then, he can do all above stuff but also able to change his "role membership".
I goggled a lot but, not find any solid solution.
Please guide how I achieve the same from GUI as well as by t-sql scripts. As I need to this for more than 20 users. Basically, all these users are developers.