0

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?

  • You may create a new role and assign these roles to these developers. grant update and delete permission to this role. – Learning_DBAdmin Feb 12 at 8:51
  • 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? – DBA Feb 12 at 8:57
  • @Learning_DBAdmin btw, they will update only on several tables – DBA Feb 12 at 8:58
  • @Learning_DBAdmin btw, they will update only on several tables – DBA Feb 12 at 8:59
  • 1
    @Learning_DBAdmin Thank you so much for your help and answer. – DBA Feb 12 at 11:43
0

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 .

Example commands:

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.

|improve this answer|||||
  • Thank you very much for the answer – DBA Feb 12 at 11:43
  • @DBA happy to! Good luck :) – Randi Vertongen Feb 12 at 11:54

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.