1

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?

3
  • Not a SQL Server man, but can't you just set permissions to SELECT only for the relevant users? – Vérace Feb 4 '20 at 7:12
  • Just give db_reader and remove all other permissions, don't need to explicitly give Deny permissions. – Ramakant Dadhichi Feb 4 '20 at 9:05
  • Your approach first should be what permission to be granted, deny comes only when grant has already been made either directly or through group. – Learning_DBAdmin Feb 4 '20 at 9:35
1

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.

enter image description here

Step2: Go to 'ÚserMapping' --> Database you want to do this for --> make sure only 'public'and 'data reader'are selected.

enter image description here

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.

1
  • So in your opinion all the users inherit their permissions only from server/database roles? What about explicit permissions? – sepupic Feb 4 '20 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.