SQLTeam.com | Weblogs | Forums

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


#1

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


#2

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.


#3

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]