List of users with DELETE privilege to a certain table

  • Guys,

    How can I find out a list of users with DELETE privilege to a certain table?

    Thanks a lot!

  • select u.name

    from sysprotects p

    inner join sysobjects o

    on o.id = p.id

    inner join sysusers u

    on p.uid = u.uid

    where p.action = 196

    and o.name = 'table_name'

  • or sp_helprotect 'table_name'

    Greg

  • Wildcat,

    I just tried this query and it returned nothing.

    I'm also curious about something else: Since administrators have privileges to everything, should all admin users be returned in this query?

    Thank you!

  • sql_er (10/2/2008)


    I just tried this query and it returned nothing.

    I'm also curious about something else: Since administrators have privileges to everything, should all admin users be returned in this query?

    I wrote it in a hurry. No tested. Sorry.

    But, DBA is not included. (DBA is the owner of that database. No need to be included.)

  • Guys,

    I decided to simplify my problem a bit, and just find out if a given user is a sysadmin.

    This was achieved using the following script:

    SELECT 'Username' = lgn.name

    FROM master.dbo.spt_values spv, master.dbo.sysxlogins lgn

    WHERE spv.name = 'sysadmin' AND spv.low = 0 AND spv.type = 'SRV' AND lgn.srvid IS NULL AND spv.number & lgn.xstatus = spv.number

    AND lgn.name = @UserName

    Thanks for all the suggestions!

  • Make use of the following function:

    IF IS_SRVROLEMEMBER ('sysadmin','loginname') = 1

    print 'User''s login is a member of the sysadmin role'

    else

    print 'User''s login is not a member of the sysadmin role'

    MJ

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply