Grant only select permissions to few members of Domain Admin group
Hi Folks, We have a Database that holds a single audit table containing the login information of users who log into our website. It conatains the ip address, datetime of login etc. Security wants to allow access to the table to be limited only to the DBA group to update, alter, delete, drop the table. We have a domain admin group with sysadmin permissions on the server. The members of this group are infrastructure staff and services that do some overnight processes. Is it possible to give only read/select permissions to all the infrastructure staff while the permissions for services remain unchanged ? Thanks
Unfortunately, no. If they are a member of the sysadmin server role, they will have the ability to do whatever they want regardless of any explicit DENY that might be present. That role will trump everything. From:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authorization-and-permissions-in-sql-server *"DENY takes precedence over all permissions, except DENY does not apply to object owners or members of sysadmin. "* I would recommend creating a new group for both the infrastructure staff as well as the service accounts. It's probably not a good idea to have the service accounts into the domain admin group anyway. Once the two groups are created, you can then create new logins and assign permissions accordingly. Hope that helps!