May 12, 2008 at 11:38 am
Does anyone know of one? We need a script that pulls privs that is not tied to a stored procedure?
Thanks
May 12, 2008 at 3:53 pm
You could just copy code you want to use out of sp_helprotect. Or search this site's script section for "list database permissions". I use this to quickly get a user's object permissions:
declare @username as varchar(50)
set @username = 'user name'
select O.name, permission_name from sys.database_permissions
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where U.name = @username
order by O.name
Greg
May 12, 2008 at 3:56 pm
database_perms.? I believe that is for only 2005. Is that correct? I'm looking for the wonderful world of 2000.
Thnx
May 13, 2008 at 12:03 pm
Yeah, sorry. Use this for SQL 2000:
declare @username as varchar(50)
set @username = 'user name'
select cast(o.name as varchar(45))as 'object'
,case p.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 224 then 'EXECUTE'
else ' '
end as 'permission'
from sysprotects p join sysobjects o on o.id = p.id
join sysusers u on p.uid = u.uid
where u.name = @username
Greg
May 13, 2008 at 1:03 pm
Silly question, to refer this back to every user, similar to help protect, would you just point that back to sysusers.name..?
May 14, 2008 at 9:12 am
Yep. Add that to the SELECT list and remove the WHERE clause.
Greg
May 14, 2008 at 9:37 am
As a follow up, here is my final working replacement for sp_helprotect on 2000
select u.name,cast(o.name as varchar(45))as 'object'
,case p.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 224 then 'EXECUTE'
when 26 then 'Reference'
else ' '
end as 'permission'
from sysprotects p join sysobjects o on o.id = p.id
join sysusers u on p.uid = u.uid
where u.name <> 'public'
UNION
select b.name, category =
CASE a.action
WHEN '198' then 'Create Table'
WHEN '203' then 'Create Database'
WHEN '207' then 'Create View'
WHEN '222' then 'Create Procedure'
WHEN '228' then 'Backup Database'
WHEN '223' then 'Create Default'
WHEN '235' then 'Backup Log'
WHEN '236' then 'Create Rule'
End, '--'
from sysprotects a, sysusers b
where b.uid = a.uid
and b.name <> 'public'
and a.action in (198,203,207,222,228,223,235,236)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply