Objects that a user can access

[Tomi]

Hi,

How can I find all the objects(Tables,views,SPs etc) that a particular SQL user login has access to (The user may not be the owner but may have rights on the objects)?

Pls help

Thanks
Tomi

 

[hmckillop]

try something like

DECLARE @UserName varchar(200)
SET @uSerName = 'MyUser'
select 
    a = o.name, 
    b = user_name(o.uid), 
    user_name(p.uid), 
    p.uid,
    o.sysstat & 0xf, 
    p.id, 
CASE action
WHEN 26  THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
ELSE '' 
END as action,

action, 
    protecttype 
from dbo.sysprotects p, 
    dbo.sysobjects o,     
    master.dbo.spt_values a 
where 
    o.id = p.id 
    and (( p.action in (193, 197) 
        and ((p.columns & 1) = 1) ) 
        or ( p.action in (195, 196, 224, 26) ))     
        and (convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high != 0) 
        and a.type = N'P' and a.number = 0  and user_name(p.uid) = @uSerName
        order by a, b

"I'm living so far beyond my income that we may almost be said to be living apart

 

[gradley]

Here's something else you can try that is a system stored procedure:

-- View granted privileges for specific user or all users within current database
exec sp_helprotect  NULL, 'User_Name'

-- View granted privileges for all users within the current database
exec sp_helprotect