October 2, 2008 at 2:08 pm
Guys,
How can I find out a list of users with DELETE privilege to a certain table?
Thanks a lot!
October 2, 2008 at 2:23 pm
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'
October 2, 2008 at 2:35 pm
or sp_helprotect 'table_name'
Greg
October 2, 2008 at 2:46 pm
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!
October 2, 2008 at 3:16 pm
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.)
October 3, 2008 at 9:09 am
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!
October 3, 2008 at 3:57 pm
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