0

There are several Active Directory user groups in my SQL Server database environment. I changed the users' permissions on all databases on the server. Several users wanted me to give them the update permission back. I used a command like this:

Grant Update To [DomainGroup\User]

But this did not work. Now no-one can update except me. How can I resolve this?

Here is the result of the query;

principal_id sid name type usage 35 0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A43140000 NETONE\ibrahim.tackin WINDOWS LOGIN GRANT OR DENY 0 0x01050000000000090400000083741B006749C04BA943C02702F2A762 public ROLE GRANT OR DENY 5 0x010500000000000904000000FD051D9B2622FA4790703C7DB2C405A8 db_executor ROLE GRANT OR DENY 6 0x0105000000000005150000008D555C5A9C99DF04AB5BAB3AF2380000 NETONE\sql-ict-ad-rms WINDOWS GROUP GRANT OR DENY 7 0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5B2B0000 NETONE\sql-ict-ad-rms-mod WINDOWS GROUP GRANT OR DENY 28 0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A292C0000 NETONE\SQLDDL WINDOWS GROUP GRANT OR DENY 32 0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A352A0000 NETONE\SQLQRY WINDOWS GROUP GRANT OR DENY 33 0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5C410000 NETONE\SQLGelir-guvence WINDOWS GROUP GRANT OR DENY 34 0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5D410000 NETONE\SQLGelir-guvence-mod WINDOWS GROUP GRANT OR DENY 16384 0x01050000000000090400000000000000000000000000000000400000 db_owner ROLE GRANT OR DENY 16390 0x01050000000000090400000000000000000000000000000006400000 db_datareader ROLE GRANT OR DENY 16391 0x01050000000000090400000000000000000000000000000007400000 db_datawriter ROLE GRANT OR DENY

  • 1
    It think you should try GRANTE UPDATE ON SCHEMA::Schema_Name to [DomainGroup\User] – Rajesh Ranjan Sep 25 at 8:46
  • What should i write instead of schema? Is it db name? – Dbo Sep 25 at 8:56
  • Please update your question with the result of execute as user = 'DomainGroup\User'; select * from sys.user_token; revert; where DomainGroup\User is any of your problem users – sepupic Sep 25 at 9:21
  • So much sorry. I couldn't exactly get it. Can you explain it a little bit more? @sepupic – Dbo Sep 25 at 10:01
  • Your domain user may be a member of more win groups and db roles. To see where he could have a deny we need to explore all his membership. To do it you should impersonate this user and check its token. This can be done in SSMS by executing the code I posted above – sepupic Sep 25 at 10:58
0

The user that has access problems is the member of many win groups, even if he's a member of db_owner he has limited access if someone revoke/deny some permissions to him or to his group.

To find out restrictions you should use this query:

execute as user = 'NETONE\İbrahim.tackin';   
select user_name(grantee_principal_id) as user_,
       permission_name,
       object_name(major_id) as obj_name,
       class_desc,
       state
from sys.database_permissions p
     join sys.user_token t
        on p.grantee_principal_id = t.principal_id
where state in ('D', 'R');
revert;

Here I limit the result to those roles/win groups of which problem user is a member of.

So your user has 3 DENY of UPDATE on all database:

NETONE\sql-ict-ad-rms UPDATE NULL DATABASE D
NETONE\SQLDDL UPDATE NULL DATABASE D
NETONE\SQLQRY UPDATE NULL DATABASE D

Your user will be unable to perform any UPDATE until you revoke these denies:

revoke update to [NETONE\İbrahim.tackin],[NETONE\SQLDDL],[NETONE\SQLQRY]

If you don't deny by error and the members of these 3 groups should not be able to update, you should exclude all your users that should be able to update from all these 3 groups.

Note you have same 3 deny to make a delete.


The permissions of any user is given by the "sum" of all the permissions that were granted/denied to him and his groups/roles.

Until your user is a member of win group that has update denied he will not be able to update. To make him be able to update you should exclude him from grous with deny or revoke deny from these groups.

  • The result set is; user_ permission_name obj_name class_desc NETONE\sql-ict-ad-rms DELETE NULL DATABASE NETONE\sql-ict-ad-rms UPDATE NULL DATABASE NETONE\sql-ict-ad-rms-mod CREATE TABLE NULL DATABASE NETONE\sql-ict-ad-rms-mod DELETE NULL DATABASE NETONE\sql-ict-ad-rms-mod UPDATE NULL DATABASE NETONE\SQLDDL DELETE NULL DATABASE NETONE\SQLDDL UPDATE NULL DATABASE NETONE\SQLQRY DELETE NULL DATABASE NETONE\SQLQRY UPDATE NULL DATABASE Now, Where will i go from here? – Dbo Sep 25 at 11:49
  • I updated my answer by including state column in the query.Could you please include the column state in the result? Even if I think it's D. Win groups NETONE\SQLDDL and NETONE\SQLQRY have UPDATE denied, and the user himself – sepupic Sep 25 at 11:53
  • Yes @sepupic. Here is the new result set; user_ permission_name obj_name class_desc state NETONE\sql-ict-ad-rms DELETE NULL DATABASE D NETONE\sql-ict-ad-rms UPDATE NULL DATABASE D NETONE\sql-ict-ad-rms-mod CREATE TABLE NULL DATABASE D NETONE\sql-ict-ad-rms-mod DELETE NULL DATABASE D NETONE\sql-ict-ad-rms-mod UPDATE NULL DATABASE D NETONE\SQLDDL DELETE NULL DATABASE D NETONE\SQLDDL UPDATE NULL DATABASE D NETONE\SQLQRY DELETE NULL DATABASE D NETONE\SQLQRY UPDATE NULL DATABASE D – Dbo Sep 25 at 12:00
  • By the way, what is the meaning of D or R? Thank you deeply very much for you helps .. – Dbo Sep 25 at 12:01
  • D means deny. You have 3 explicit DENY on UPDATE. I'll update my answer now – sepupic Sep 25 at 12:03

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.