I want to deny all ddl and dml commands to AD and SQL Server users over all the databases on my database server. How can i do that?
Sql-server – Deny All DDL & DML Commands In Sql Server
ddlsql serversql-server-2008-r2sql-server-2012sql-server-2016
Related Question
- Sql-server – SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables
- Sql-server – Server Wide DDL Trigger Permissions Issue
- Sql-server – Deny access to one database, allow all the rest
- SQL Server Security – Deny Delete to Active Directory Account
- Sql-server – Deny a Sql Server user from a specific hostname
Best Answer
Well...the AD part is unnecessary, that only gives authentication/login privileges to users (basically all AD does in SQL Server is tell SQL Server "I have verified that this person is who she/he says it is and you can allow this person to connect because I say so" - this is a very basic explanation, of course).
If you want to set up privileges about what data users can see and what they can or cannot execute on your SQL Server it is enough to do this in SQL Servers itself. Also, I would not over-complicate things with 'deny', just 'revoking the permission'is more than enough. If they don't have the right to execute DDL and DML, they will not be able to do so, deny is unnecessary.
Here is how to do it:
Step 1. Go to your SQL instance -> Security-> right click the login of the user / group you want to do this for, then on Properties and in your newly opened window got to 'ServerRoles' and make sure only public is selected.
Step2: Go to 'ÚserMapping' --> Database you want to do this for --> make sure only 'public'and 'data reader'are selected.
Step3: Repeat this for all DBs you need to give permission to the user for data reading, but not DML & DDL statements.
Do the same for all other logins you need to do this for.
In SQL Server 2016 or newer make sure you also check the DB user because they decoupled the server level permission from the database level permissions. So you can deny this on server level, but you have this set up on a DB level you and then your sever level setting has no effect. You can check the database level permissions by going to Database --> Security --> right click on the user you are interested in --> properties. Then on your newly opened window go to both 'owned schemas' and 'membership' and make sure nothing or only 'db_reader'are selected.
You find documentation here and here.
Also, you might want to check this and this out - it shows some examples of how you can get a list of the logins,users and permissions - it will make your life a lot easier.