I have a Active Directory user account named SQLQRY in my sql server. SQLQRY is permitted only read data. Those users can use onyl select statement. I need to give just update and delete permission on spesific tables to several developers who are in the SQLQRY AD account. How can i do that without extracting them out of the group?
I need to give just update and delete permission on specific tables to several developers who are in the SQLQRY AD account. How can i do that without extracting them out of the group?
If you are using the same AD user for all your developers I would advise you to start using AD groups and adding your developers to these groups. See here for a Q/A on using one AD login for multiple users. Afterwards you could follow the steps below.
Permissions are granted to all members of the AD group or none.
If you want to grant permissions to some of these users, you would have to add a different ad group or add the users separately.
Afterwards you can grant the update and delete permissions to that group or create a new role with the permissions and add the group / users to it .
USE [master] CREATE LOGIN [SQLQRY_2] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO USE [DBName] CREATE USER [SQLQRY_2] FOR LOGIN [SQLQRY_2]; CREATE ROLE [NewRole]; GRANT INSERT,UPDATE,DELETE ON dbo.test to [NewRole]; -- table1 -- Table 2,3, ... ALTER ROLE [NewRole] ADD MEMBER [SQLQRY_2];
They are defined in SQLQRY AD account and they don't have update and delete permissions out there. So, will my role overwrite to my deny rule?
If you have deny permissions specified on writing data to the tables then granting corresponding write permissions will not work. DENY has a higher precedence than grant.