How Avoid DML Operation For Sysadmin User..?
Can you please provide more information?
I have sysadmin user in sql server and have morethan 10 database in instance but i want avoid DML operation(Data security) for all database...?
Just so I understand ... you want to prevent a SQL Server system administrator from being able to change data in a database?
I don't think there's a way to do that.
You can write DML triggers that disable all DML. You could write a DML trigger that rolled back any DML based on user. But I don't think you can do it with permissions only.
If the sysadmin Login was mapped to a User in the database, you could add the User to the db_denydatawriter role. The issue is that sysadmin would not necessarily get mapped to a User at all. Overall, it sounds like it could cause more problems than it would solve, IMO.
I've got some interesting ideas in here:
Constraints should perform better than triggers, they have less overhead. They're also a bit sneakier. While nothing can prevent the sysadmin from disabling them, you could always add a lot of constraints on the tables to do the same thing. The overhead is minimal as the first constraint evaluated will fail immediately, and none of them evaluate against data.
Of course, the best suggestion is to remove them from the sysadmin role if they're not supposed to be doing DML. You can also try putting them in db_denydatawriter but I'm not sure it works properly for sysadmin members, you'll have to test it.
The option available in Sql server 2014"SELECT ALL USER SECURABLES” permission for the securable data for dba but how to implemented in sql server 2012..?