Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 List Securables by User

Author  Topic 

swoozie
Starting Member

25 Posts

Posted - 2014-07-14 : 15:46:09
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

38200 Posts

Posted - 2014-07-14 : 16:21:31
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

25 Posts

Posted - 2014-07-15 : 15:27:58
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

38200 Posts

Posted - 2014-07-15 : 15:41:49
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

25 Posts

Posted - 2014-08-08 : 10:20:14
Thanks! Whatever happened to uniform terminology?

Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-08 : 19:19:14
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

swoozie
Starting Member

25 Posts

Posted - 2015-03-18 : 11:25:57
I had found to answer to my questio, create an awesome script, and like an idiot I did not post a reply for myself. I now have a similar issue and I am not able to figure it out it is slightly differnent.

I still need a list of Securables\permissions(explicit) but in this manner:

Table, Permission, Role

the issue I am having is findig all of the possiblities for the permissions. i.e., role, User Role, Public, Etc.. I have tried a few itterations and the tables appear to have no explicit permissions, however the report from last year (where the quesy no longer exists) has permission listed on the tables. I know permissions havent changed that drasticaly over the past year so I am a little stumped on why I can not get close to matching the report.

I am using a variation on this:


SELECT s.name AS [Schema], o.name AS Object, u.name AS [User],
dp.permission_name, dp.state_desc
FROM sys.database_permissions dp
JOIN sys.objects o ON dp.major_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals u ON dp.grantee_principal_id = u.principal_id
UNION ALL
SELECT s.name AS [Schema], NULL, u.name AS [User],
dp.permission_name, dp.state_desc
FROM sys.database_permissions dp
JOIN sys.schemas s ON dp.major_id = s.schema_id
JOIN sys.database_principals u ON dp.grantee_principal_id = u.principal_id
ORDER BY s.name, o.name, u.name

--SELECT u.name AS [User], r.name AS Role
--FROM sys.database_role_members rm
--JOIN sys.database_principals u ON rm.member_principal_id = u.principal_id
--JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
--ORDER BY u.name, r.name
Go to Top of Page
   

- Advertisement -