question

mail_sady avatar image
mail_sady asked

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
securitydbasql 2012sql security
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi JohnM, Thank you for your reply. The link is very clear on that. Two groups seems the way out so I will put forth the proposal which I doubt the team has appetite for.
0 Likes 0 ·
JAbsalom avatar image
JAbsalom answered
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi JAbsalom, Thank you for the link. I will try out the sql audit as it is the next best thing to having 2 groups created.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.