The below queries display information which answers the question:
Who has access to what objects?
-- Display all privileges which have been granted to users or groups
select u.name,v.name
from sysprotects p, master.dbo.spt_values v, sysusers u
where p.uid=u.uid
and p.actionfiltered=v.number
and p.protecttype=1
and v.type = 'T'
and u.name!= 'dbo'
go
-- Display objects which have 'update' granted to them
select crdate,type,name from sysobjects where id in
(
select p.id
from sysprotects p, master.dbo.spt_values v, sysusers u
where p.uid=u.uid
and p.actionfiltered=v.number
and p.protecttype=1
and v.type = 'T'
and u.name!= 'dbo'
and v.name = 'Update'
)
go
-- Display objects which have non-standard access granted to them.
-- This might include UPDATE STATISTICS or TRUNCATE
select crdate,type,name from sysobjects where id in
(
select p.id
from sysprotects p, master.dbo.spt_values v, sysusers u
where p.uid=u.uid
and p.actionfiltered=v.number
and p.protecttype=1
and v.type = 'T'
and u.name!= 'dbo'
and v.name not in ('Delete','Execute','Insert','References','Select','Update')
)
go
|
|
