Assign DML,DDL to login but not alter login, backu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Assign DML,DDL to login but not alter login, backu

Author

Topic

bsethi24
Starting Member

25 Posts

Posted - 2012-08-02 : 10:05:10

Dear All,

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 10:23:11

create a role and grant only require accesses to it using GRANT t-sql statement

then assign required users to this role

that way you dont have to repeat the steps for each and every user

you can use CREATE ROLE to create a role

GRANT ROLE to grant role to user

and

GRANT <required priviledge> TO <rolename>

for giving role a particular priviledge

object priviledges can be like INSERT,SELECT,UPDATE etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Krishna_DBA
Starting Member

4 Posts

Posted - 2012-08-02 : 10:31:47

you can add the user to db_datareader, db_datawriter and db_ddladmin roles for this. This should allow the user to do only the tasks that he/she needs to do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources