Login Has db_datareader But user is Able to Update 1

[JohnBates]

hi experts,

One of the logins on this (2005) server has only 1 database role: db_datareader. Awhile back, I changed him from db_owner to db_datareader.

**Recently we discovered that he can update a table.** I have re-confirmed that he has only the db_datareader role.
The network admin assures me that that this user is not a member of any domain group that would have update permissions.

Are there some other things I can check?

Thanks, John

 

[Jamfool]

How can he update? Using an update statement or via stored procedures? Also have you checked object level permissions.

 

[JohnBates]

He can update by simply executing an SQL statement. I will check his object level permissions for that table today.

Thanks, John

 

[JohnBates]

hmmmmm.

At the database level, the user has only Connect and Select:

USE AVNAPPDB;
EXECUTE AS USER = 'CORPORATE\userX';
SELECT *
FROM fn_my_permissions(NULL, 'Database')
ORDER BY subentity_name, permission_name ;
--REVERT;

Results:

database CONNECT
database SELECT


But when I look at his permissions for specific tables, he can Update !

USE AVNAPPDB;
EXECUTE AS USER = 'CORPORATE\userX';
SELECT * FROM fn_my_permissions'dbo.LO_LOHDR_LOAN_ORDER_HDR', 'Object');

results:

entity_name subentity_name permission_name
dbo.LO_LOHDR_LOAN_ORDER_HDR SELECT
dbo.LO_LOHDR_LOAN_ORDER_HDR UPDATE
dbo.LO_LOHDR_LOAN_ORDER_HDR REFERENCES
dbo.LO_LOHDR_LOAN_ORDER_HDR INSERT
dbo.LO_LOHDR_LOAN_ORDER_HDR DELETE

?? This user has only the db_datareader role so how is he getting update permissions for the tables? I have dropped and recreated his SQL Server login and he still has update permissions. Any ideas? this is 2005.

Thanks, John

 

[Jamfool]

Check to see if there are any permissions assigned to the public role etc.

select p.ID, u.Name as UserName, obj.name as ObjectName, 
	case p.ProtectType
	when 204 then 'GRANT_W_GRANT'
	when 205 then 'GRANT'
	when 206 then 'REVOKE'
	end as ProtectType,
	case p.action
	when 26 then 'REFERENCES'
	when 178 then 'CREATE FUNCTION'
	when 193 then 'SELECT'
	when 195 then 'INSERT'
	when 196 then 'DELETE'
	when 197 then 'UPDATE'
	when 198 then 'CREATE TABLE'
	when 203 then 'CREATE DATABASE'
	when 207 then 'CREATE VIEW'
	when 222 then 'CREATE PROCEDURE'
	when 224 then 'EXECUTE'
	when 228 then 'BACKUP DATABASE'
	when 233 then 'CREATE DEFAULT'
	when 235 then 'BACKUP LOG'
	when 236 then 'CREATE RULE'
	end as PermissionGranted
from sysprotects p inner join sysusers u on p.Uid = u.UID inner join sysobjects obj on p.ID=obj.ID
where obj.Name like 'LO_LOHDR_LOAN_ORDER_HDR'

 

[JohnBates]

Thanks Jamfool.

This is what it shows:

ID UserName ObjectName ProtectType PermissionGranted
1403333701 public LO_LOHDR_LOAN_ORDER_HDR GRANT DELETE
1403333701 public LO_LOHDR_LOAN_ORDER_HDR GRANT INSERT
1403333701 public LO_LOHDR_LOAN_ORDER_HDR GRANT REFERENCES
1403333701 public LO_LOHDR_LOAN_ORDER_HDR GRANT SELECT
1403333701 public LO_LOHDR_LOAN_ORDER_HDR GRANT UPDATE



So I assume this user has update permissions because the Public user has those?

What is the best way to fix this? Deny update,insert,delete to Public or Deny update, insert, delete to each specific user?

Thanks, John

 

[Jamfool]

The public role is basically used to give blanket permissions to all users (including guest), so it is best to try to avoid assigning any permissions to it. its like the nt group Everyone.

The best way to fix it would be to remove the permissions from the public role via revoke.

However if you have concerns that your application may make use of these permissions, another option would be to create a new role and assign all the permissions that the public role has to it. Then make sure that all the existing users are a member of this new role. Except the one that you dont want to allow to update the table.