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.
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/ |
 |
|
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? |
 |
|
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/ |
 |
|
swoozie
Starting Member
25 Posts |
Posted - 2014-08-08 : 10:20:14
|
Thanks! Whatever happened to uniform terminology?
|
 |
|
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 |
 |
|
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 |
 |
|
|
|
|
|
|