April 7, 2011 at 9:30 am
I would like to know how to create a query to get a list of username, in what role he is a member , all the objects(tables, view etc.) , the name of the objects and all the permissions he got on that object.
Purpose :
I've made an application where you can chose a sqlserver(combobox1), then you got the option of selecting a database(combobox2) available in that server and at last all users of that DB will be listed in a third combobox.
when you click on a user i would like to have the result of the query as explained at the top in a gridview.
so far i got this :
SELECT b.name as USERName, c.name as RoleName, o.Type_desc as [Object Type], o.name as [Object Name],Permission_name as [Permission] FROM sys.database_permissions dp JOIN sys.objects o on dp.major_id = o.object_id join sys.database_principals dpl on dp.grantee_principal_id = dpl.principal_id join sys.database_role_members dr on dr.member_principal_id = dpl.principal_id join dbo.sysmembers a on dpl.principal_id = a.memberuid join dbo.sysusers b on a.memberuid = b.uid join dbo.sysusers c on a.groupuid = c.uid WHERE class = 1 AND o.type in ('U','P','V','Fn') AND dp.type in ('SL','IN','UP','EX') and dpl.name = '" & user & "' ORDER BY o.Type_desc,c.name, o.name.
Please help. Thanks in advance!
grtzzzzz
April 7, 2011 at 12:29 pm
I had posted a script in this forum post (http://www.sqlservercentral.com/Forums/Topic745531-359-1.aspx#bm746408) that retrieves all the data you are looking for. It does not do it all in one query but you may be able to use it as a reference
April 7, 2011 at 2:14 pm
You can try this, Still figuring out the dbRole column
SELECT
[Login Type]=
case sp.type
when 'u' then 'WIN'
when 's' then 'SQL'
when 'g' then 'GRP'
end,
sp.Name as srvLogin ,
sp2.Name as srvRole,
dbp.Name as dbUser,
NULL as dbRole,
s.name as ObjName,
s.xtype as ObjType,
D.permission_name as ObjPermission
FROM sys.sysobjects S inner join
sys.database_permissions d on d.major_id = S.[id] inner join
sys.database_principals dbp on dbp.principal_id=d.grantee_principal_id inner join
sys.server_principals as sp on sp.sid=dbp.sid inner join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id inner join
sys.server_principals as sp2 on sp2.principal_id=srm.role_principal_id left join
sys.database_principals as dbp2 on srm.role_principal_id=dbp2.principal_id
WHERE s.type in ('U','P','V','Fn') and class=1
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply