ACLs for logins
-
hi ,
i need to check object level permissions for a particular logins/roles , i can not check throum EM since there are lot of logins so can anybody help me out on this with any script or other way.
Thanks
-
hi there:
You can use the followings script, fist script will give you list of permissions on tables/views; second one will list permissions for the Stored procedures
/*List permissions on tables*/
--
--delete the 'work' table
--
drop table #T1
go
--
--populate the 'work' table
--
select case
when issqluser=1 then 'SQL User'
when isntuser=1 then 'NT User'
when isntgroup=1 then 'NT Group'
when isntname=1 then 'NT Group or User'
when issqlrole=1 then 'SQL Group/Role'
end 'User Type',
user_name(pro.uid) 'User/Group Name',
--pro.id,
--obj.id,
obj.name,
--pro.uid,
--pro.action,
case
when pro.action = 193 then 'X'
else ' '
end as 'SELECT',
case
when pro.action = 195 then 'X'
else ' '
end as 'INSERT',
case
when pro.action = 196 then 'X'
else ' '
end as 'UPDATE',
case
when pro.action = 197 then 'X'
else ' '
end as 'DELETE'
into #T1
from sysprotects pro
join sysobjects obj
on pro.id=obj.id
join sysusers su
on pro.uid=su.uid
where obj.type in('U','V')
and pro.action in (193, 195, 196, 197)
go
--
--Results in table Name, User Type, User/Group name order
--
select cast([name] as char(30)) 'Table Name',
[User Type],
cast([User/Group Name] as char(30)) 'User/Group Name',
max([SELECT]) 'Select',
max([INSERT]) 'Insert',
max([UPDATE]) 'Update',
max([DELETE]) 'Delete'
from #t1
group by
[name],
[User Type],
[User/Group Name]
go
/*list permissions per SP*/
drop table #t2
go
select case
when issqluser=1 then 'SQL User'
when isntuser=1 then 'NT User'
when isntgroup=1 then 'NT Group'
when isntname=1 then 'NT Group or User'
when issqlrole=1 then 'SQL Group/Role'
end 'User Type',
user_name(pro.uid) 'User/Group Name',
--pro.id,
--obj.id,
obj.name,
--pro.uid,
--pro.action,
case
when pro.action = 224 then 'X'
else ' '
end as 'EXECUTE'
into #t2
from sysprotects pro
join sysobjects obj
on pro.id=obj.id
join sysusers su
on pro.uid=su.uid
where obj.type='P'
and pro.action in (224)
go
--results by proc name
--
select cast([name] as char(30)) 'Procedure Name',
[User Type],
cast([User/Group Name] as char(30)) 'User/Group Name',
[Execute]
from #t2
order by
[name],
[User Type],
[User/Group Name]
go
Please feel free to alter these scripts to meet your requirements; let me know what changes you have made...
HTH,
Thnx/Rgds,
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply