June 1, 2012 at 9:11 am
Hi Team,
We have Two Environments QA and IST. Here we have ABC user we have
How Can we check ABC user What permissions he have on which databases.
Any scripts please suggest me .
My Expected OUTPUT:-
User name Database name Roles
aaa viki DataReader,logreader,datawrited
bbb biki Datawriter.dataowner
In this way i m expecting,
Thanks in advance.
June 1, 2012 at 11:05 am
For database role,
select a.name DBUserName,
(select x.Name from sys.sysusers x where x.uid=b.groupuid) AS AssociatedRole ,
Isnull((SELECT top 1 Name FROM master..syslogins sl WHERE a.sid = sl.sid or a.name = 'Public'),'') LoginName,
HasDBAccess
from sys.sysusers a
LEFT OUTER JOIN sys.sysmembers b ON a.uid=b.memberuid
where isnull(a.altuid,0)<>1 --and a.uid not in (1,2)
and a.issqlrole <> 1 and a.isapprole <> 1
and a.Name not like '##%##'
For Database permissions,
select
ISNULL(A.UserName,B.UserName) UserName, ISNULL(A.ObjectName,B.ObjectName) ObjectName,A.GRANTED,B.DENIED
from
(
select t1.UserName, t1.ObjectName as ObjectName, substring(
(select (', ' + GRANTED)
from (select U.name as UserName, O.name as ObjectName, p.permission_name GRANTED
from sys.database_permissions P
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where p.state_desc='GRANT'
) t2
where t1.UserName=t2.UserName and t1.ObjectName=t2.ObjectName
Order by UserName, ObjectName
for XML Path ('')
), 3 , 1000) GRANTED
from (select U.name as UserName, O.name as ObjectName, p.permission_name GRANTED
from sys.database_permissions P
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where p.state_desc='GRANT'
) t1
Group by t1.UserName, t1.ObjectName) A
FULL JOIN
(
select t1.UserName, t1.ObjectName as ObjectName, substring(
(select (', ' + DENIED)
from (select U.name as UserName, O.name as ObjectName, p.permission_name DENIED
from sys.database_permissions P
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where p.state_desc='DENY'
) t2
where t1.UserName=t2.UserName and t1.ObjectName=t2.ObjectName
Order by UserName, ObjectName
for XML Path ('')
), 3 , 1000) DENIED
from (select U.name as UserName, O.name as ObjectName, p.permission_name DENIED
from sys.database_permissions P
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where p.state_desc='DENY'
) t1
Group by t1.UserName, t1.ObjectName
) B on A.UserName=B.UserName and A.ObjectName=B.ObjectName
You have to also check for server fixed role and server level permissions
June 1, 2012 at 11:27 am
You cannot get all database information in one select, you have to run select on individual databases.
You may take resultset for individual database into a temp table and then using xml logic (refer database permissions script) generate comma seperated list
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply