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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy