SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 List Securables by User
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swoozie
Starting Member

USA
21 Posts

Posted - 07/14/2014 :  15:46:09  Show Profile  Reply with Quote
I am hoping someone can clarify this for me. I seem to be missing something on this topic between actual securable and user permissions.

I am trying to list All Securables assigned\granted etc.... to a user. All my research takes me back to permissions given to roles and users.and I am not correlating what I see in the results sets to what I see in the GUI.

SO I am having a disconnect.

I really just want a script that lists user name and securable.

Thanks In Advance.

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 07/14/2014 :  16:21:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
How about this?

SELECT Us.name AS username, Obj.name AS object, dp.permission_name AS permission, dp.state_desc
FROM sys.database_permissions dp
JOIN sys.sysusers Us
ON dp.grantee_principal_id = Us.uid
JOIN sys.sysobjects Obj
ON dp.major_id = Obj.id
ORDER BY Us.name, Obj.name


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

swoozie
Starting Member

USA
21 Posts

Posted - 07/15/2014 :  15:27:58  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

How about this?

SELECT Us.name AS username, Obj.name AS object, dp.permission_name AS permission, dp.state_desc
FROM sys.database_permissions dp
JOIN sys.sysusers Us
ON dp.grantee_principal_id = Us.uid
JOIN sys.sysobjects Obj
ON dp.major_id = Obj.id
ORDER BY Us.name, Obj.name


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/




So I can sound stupid, just to clarify since guis look different than table data. The Object is the same thing as the Securable right?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 07/15/2014 :  15:41:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
I am not sure how Microsoft uses the term "securable", but I think the answer is yes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

swoozie
Starting Member

USA
21 Posts

Posted - 08/08/2014 :  10:20:14  Show Profile  Reply with Quote
Thanks! Whatever happened to uniform terminology?

Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 08/08/2014 :  19:19:14  Show Profile  Reply with Quote
We use something along to lines of what you are asking for. It goes back to the login, looks for rights/roles assigned at that level, maps to the database and recurses through roles memberships and finally adds the explicit grants. It's a bit obtuse but perhaps you'll find it of use:
-----------------------------------------------------------------------------
--- 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,

	@sqlStmt	nvarchar(max) = N'',
	@Login		sysname = N'',
	@template	nvarchar(max) = N'exec xp_logininfo ''<<UserName>>'', ''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
	--('role_DenyWrite'),			--<<< Populate with a list of Users or Roles
	--('XYZ\WINDOWSGROUP'),
	('XYZ\WINDOWSUSER'),
	--('SqlLogin'),
	('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, '<<UserName>>', @Login);

		truncate table #loginInfo;

		insert into #loginInfo (
			accountName,
			type,
			privilege,
			MappedLoginName,
			PermissionPath
			)
		exec sp_ExecuteSQL @sqlStmt

		--/**/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

--/**/select 'Agg Users' Label, * from @Users

-----------------------------------------------------------------------------

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




Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000