Datareader role allowing all permissions to be effective, not just select

I have one database on my server that when granting a user the datareader role, they have effective permissions to everything. Is it possible the datareader role for that database has been modified? If so where and how to I reset the datareader role to have only data reader permissions? I have several other databases on this server and this is the only one with this issue.
I'm using 2008r2

No, the read role alone is not giving them extra permissions. But permissions get combined. That user(s) must belong to another group or role that is giving them additional permissions.

1 Like

I use this script to ferret out where Users are getting their rights from. On line 58 you change/insert you User or Users. Run the script in the current database. The output will list the effective rights and what they are derived from. The script is a bit long winded but useful.[code]-----------------------------------------------------------------------------
--- Rights Granted: Given a list of Users in the current database, return
--- the list of effective rights (including Deny) based on the Grants
--- issued to the User, all Roles the User is a member of, membership
--- in fixed database Roles (e.g., db_datareader) and membership in any
--- fixed server roles.

--- BUG/FEATURE: Doesn't account for the fact that each Login is de facto a member of Public.

set nocount on;


--- Declarations

declare
@AllUsers bit = 0, --<<< SET THIS VALUE
-- 1 = Gather for ALL Users
-- 0 = Gather for explicit list in @Users table
@ShowRaw bit = 0,

@PKey		int = 1,
@MaxPKey	int = 0,

@error_number	int,
@error_message	nvarchar(2048),

@sqlStmt	nvarchar(max) = N'',
@Login		sysname = N'',
@template	nvarchar(max) = N'exec xp_logininfo ''<<LoginName>>'', ''all''';

if object_id('tempdb..#loginInfo') is not null
drop table #loginInfo;

create table #loginInfo (
accountName sysname, -- Fully qualified Windows account name.
type char(8), -- Type of Windows account. Valid values are user or group.
privilege char(9) null, -- Access privilege for SQL Server. Valid values are admin, user, or null.
MappedLoginName sysname, -- For user accounts that have user privilege, mapped login name
-- shows the mapped login name that SQL Server tries to use when
-- logging in with this account by using the mapped rules with
-- the domain name added before it.
PermissionPath sysname -- Group membership that allowed the account access.
);

declare @Users table (
pkey int identity(1, 1),
UserName sysname
)


--- Define the User(s) of interest

insert into @Users (UserName)
values
('<>'), -- <<< <<< <<< CHANGE THIS VALUE !!! !!! !!!
('public')

set @MaxPKey = @@rowcount;


--- Find Logins that are members of Windows Groups and add the Group to the
--- set of Logins

while (@PKey <= @MaxPKey)
begin
select @Login = UserName
from @Users
where pkey = @PKey

if exists(select *
		from sys.server_principals sp
		where sp.name = @Login
		and sp.type = 'U'
		) 
	or
	not exists(select *
		from sys.server_principals sp
		where sp.name = @Login
		) 
begin
	set @sqlStmt = replace(@template, '<<LoginName>>', @Login);

	truncate table #loginInfo;

	begin try
		insert into #loginInfo (
			accountName,
			type,
			privilege,
			MappedLoginName,
			PermissionPath
			)
		exec sp_ExecuteSQL @sqlStmt
	end try
	begin catch
		declare @dbName	sysname;

		select
			@dbName	= db_name(),
			@error_number = ERROR_NUMBER(),
			@error_message = ERROR_MESSAGE();

			if @error_number <> 1332
				Raiserror(N'ERROR: Find Rights Granted(%s) failed (%i) - %s', 10,0, @dbName, @error_number, @error_Message) with LOG;
	end catch

	--/**/select * from #loginInfo;

	insert into @Users(UserName)
	select l.PermissionPath
	from #loginInfo l
	left outer join
		@Users u
			on	u.UserName = l.PermissionPath
			and	l.type = 'user'
	where
		u.UserName is null
end

set @PKey += 1;

end


if @ShowRaw = 1
begin
select
@@servername SrvName,
db_name() DbName,
d.name dn,
s.sid ServerSID,
d.sid DB_SID,
case
when s.sid = d.sid then 1
else 0
end is_equal
from
sys.server_principals s
inner join
sys.database_principals d
on d.name = s.name
where
@AllUsers = 1
or
s.Name in (
select UserName
from @Users
)
end


;with AllRoles -- Recursively find all Roles the Users are members of
as (
select
dp.principal_id,
dp.name,
dp.sid,
cast('' as sysname) MemberName,
cast(dp.name as sysname) Lineage,
dp.name BaseName
from sys.database_principals dp
left outer join sys.server_principals sp
on dp.sid = sp.sid
--where
-- dp.type in ('S', 'U', 'G')
--or dp.name = 'public' -- Public is special since it is not in sys.database_role_members

union all

select 
	r.principal_id, 
	r.name,
	r.sid, 
	rm.Name, 
	cast(r.Name + N'.' + Lineage as sysname), 
	ar.BaseName
from
	AllRoles ar
inner join
	sys.database_role_members drm
		on	drm.member_principal_id = ar.principal_id
inner join
	sys.database_principals r
		on	r.principal_id = drm.role_principal_id
inner join
	sys.database_principals rm
		on	rm.principal_id = drm.member_principal_id

)
select a.*
from (
-------------------------------------------
--- Object Level Rights: Explicit Grants
--- (Based on User and Role Memberships)
-------------------------------------------

SELECT 'Explicit Grants' How,
	coalesce(so.name, '.') AS 'Object Name', 

	sp.permission_name,
	state_desc,

	u.Name Grantee,
	ar.Lineage,
	ar.BaseName
FROM
	sys.database_permissions sp					-- Rights Granted
inner join
	sys.database_principals u					-- Grantee
		on	sp.grantee_principal_id = u.principal_id

left outer join
	sys.objects so								-- Object
		on	so.object_id = sp.major_id

inner join
	AllRoles ar
		on	u.sid = ar.sid

WHERE 
	(
	so.name is Null
or
	LEFT(so.name,3) NOT IN ('sp_', 'fn_', 'dt_', 'dtp', 'sys')
	--AND
	--so.type IN ('U','V','TR','P','FN','IF','TF')
	)
--and	not (
--		sp.class_desc = 'DATABASE'
--	and sp.permission_name = 'CONNECT'
--	)
and	sp.major_id >= 0					-- Negative => System Object
and (
	ar.BaseName = 'public'
	or
	@AllUsers = 1
	or
	ar.BaseName in (
		select UserName
		from @Users
		)
	)

union --all

-------------------------------------------
--- Fixed Database Role Membership
-------------------------------------------

select 'Fixed Database Role' How,
	ar.Name,
	'.',
	'.',
	ar.MemberName,
	ar.Lineage,
	ar.BaseName
from
	AllRoles ar
inner join
	sys.database_principals r
		on	r.principal_id = ar.principal_id
		and	r.is_fixed_role = 1
where
	@AllUsers = 1
	or
	ar.BaseName in (
		select UserName
		from @Users
		)

union --all

-------------------------------------------
--- Fixed Server Role Membership
-------------------------------------------

select 'Fixed Server Role' How,
	sr.Name,
	'.',
	'.',
	'Server Role',
	'.',
	l.Name
from sys.server_principals l
inner join
	sys.server_role_members r
on
	r.member_principal_id = l.principal_id
inner join
	sys.server_principals sr
on
	sr.principal_id = r.role_principal_id
and	sr.type = 'R'
where
	@AllUsers = 1
	or
	l.name in (
			select UserName
			from @Users
			)

union --all

-------------------------------------------
--- Explicit Server Level Rights
---	(Based on Login)
-------------------------------------------

select 'Explicit Server: Login' How,
	'Server',
	sp.permission_name,
	sp.state_desc,
	l.Name,
	'.',
	l.Name
from
	sys.server_permissions sp				-- Rights Granted
inner join
	sys.server_principals l					-- Grantee
		on	sp.grantee_principal_id = l.principal_id
where
	sp.permission_name <> 'CONNECT SQL'
and	(
	@AllUsers = 1
	or
	l.name in (
			select UserName
			from @Users
			)
	)

union --all

-------------------------------------------
--- Explicit Server Level Rights
---	(Based on Login -> Server Role)
-------------------------------------------

select 'Explicit Server: Role' How,
	'Server',
	sp.permission_name,
	sp.state_desc,
	l.Name,
	'.',
	l.Name
from
	sys.server_permissions sp				-- Rights Granted
inner join
	sys.server_principals sr
		on	sp.grantee_principal_id = sr.principal_id
		and	sr.type = 'R'					-- Grantee is Server Role
inner join
	sys.server_role_members srm
		on	sr.principal_id = srm.role_principal_id
inner join
	sys.server_principals l					-- Login is member of Role
		on	srm.member_principal_id = l.principal_id
where
	sp.permission_name <> 'CONNECT SQL'
and	(
	@AllUsers = 1
	or
	l.name in (
			select UserName
			from @Users
			)
	)
) a

order by
BaseName,
case
when permission_name = '' then 1 else 2 end,
[Object Name],
permission_name,
state_desc,
Grantee[/code]

2 Likes