Sql-server – How to Grant A User Under A Sql Domain Account To Insert and Update Records

permissionssql serversql server 2014sql-server-2012sql-server-2016

I have a sql domain account in sql server like DOMAIN\GroupName. There are many user accounts under that account like DOMAIN\user.name. I want to give insert and update permission only to a user under that domain account. How can i do it for only one user?

Best Answer

Sounds like DOMAIN\GroupName is a group and DOMAIN\user.name is a member of that group. This means that DOMAIN\user.name has access to SQL Server through that group. You have 2 options:

  1. Create another group, let's say DOMAIN\NewGroupName and grant that group INSERT and UPDATE, then make DOMAIN\user.name a member of that group. This is not the fastest solution, but it's a clean solution and it will make your life easy long term if you will get more and more users asking for the same rights as DOMAIN\user.name. You will need rights on AD (I assume DOMAIN\NewGroupName is an AD group.)
  2. Just create an individual login for DOMAIN\user.name and grant him access + UPDATE and INSERT to that DB. You find documentation on how to create the login here. After you have created the login, just right click on it and go to properties --> user mapping --> select the desired database --> select db_datawriter --> click ok. You find more about database roles and access here. If you need that user to have access to all DBs on that server, you can look at server roles (listed on the properties page right above user mapping) and you have some nice documentation about that here.
Related Question