Sql-server – Can’t Grant Users Update permission

permissionssql serversql-server-2012

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

Best Answer

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.

Related Solutions

Sql-server – User can alter procedures without permission

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE 
(
  dp.type <> 'R' 
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY 
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;

Sql-server – db_owner Member Can’t Delete on Tables

Unless the users is mapped to the sysadmin fixed server role (where they will connect to the database as the user dbo) then they can be limited by security on the database. (i.e. A member of db_owner will receive a security check where dbo will not.)

A quick test to see if it is a permissions issue is to temporarily grant a user sysadmin role and then see if they can delete data.... if not then I'd start looking for constraints on tables.

If they can then run the following against the database (you may get more you may need to add DELETE to your where clause):

select * from sys.database_permissions 
where state_desc = 'DENY'

If permissions are set it will give you something like:

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                                                  state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
0     DATABASE                                                     0           0           10                   1                    DL   DELETE                                                                                                                           D     DENY
1     OBJECT_OR_COLUMN                                             1501607433  0           10                   1                    DL   DELETE                                                                                                                           D     DENY

(2 row(s) affected)

If deny delete is set at the DATABASE level then well then there is your problem. Adjust permissions so required users are not actually DENYed access.

Best Answer

Related Solutions

Sql-server – User can alter procedures without permission

Sql-server – db_owner Member Can’t Delete on Tables

Related Question