table security - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

table security

Author

Topic

rico_bano
Starting Member

35 Posts

Posted - 2007-04-13 : 10:09:28

Can anyone tell me why i can still query a table i have denied select accesss to from a web page. setting deny access on the stored procedure works, but not if i just set the security on the table itself.

Any help is appreciated as this is confusing me.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-13 : 10:50:55

How did you set that? Deny for specific user?

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-04-13 : 10:53:11

Try running this to be sure you have the right permissions:


select o.name Object, user_name(o.uid) 'Schema', user_name(p.uid), 
	case action when 193 then 'Select' when 195 then 'Insert' when 196 then 'Delete' when 197 then 'Update' when 26 then 'DRI' when 224 then 'Exec' end action,
	case when protecttype = 206 then 'Deny' else case when protecttype = 205 then 'Grant' else null end end 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 GOES HERE'
order by 1, 2