Re: SQL Server 2005 Security

Found the problem. I forgot I had denied access to this table to the
role Public. As soon as I took the deny off the table it works.



On Apr 11, 11:12 am, cben...@xxxxxxxxxxxxxxx wrote:
I am at a loss on setting permissions in SQL Server 2005. I cannot
seem to get the most simple thing to work.

Given a table dbo.zstlblSECMethod in database Lab_data I did the
following trying to test security

CREATE User ClrUser FOR LOGIN [ClrUser]

CREATE ROLE Cleared Authorization db_securityadmin

CREATE User ClrUser FOR LOGIN [ClrUser]
sp_addrolemember 'Cleared', 'ClrUser'

GRANT EXECUTE ON dbo.TestExecuteAs TO CLEARED (this stored procedure
executes a select on zstblSECMethod with EXECUTE AS OWNER)

I log in as ClrUser and execute the stored procedure. No problem--it
works fine as expected

Now I do the following:
GRANT DELETE ON [dbo].[zstblSECMethod] TO [Cleared]
GRANT INSERT ON [dbo].[zstblSECMethod] TO [Cleared]
GRANT SELECT ON [dbo].[zstblSECMethod] TO [Cleared]

But when I try "SELECT * from [dbo].[zstblSECMethod]; I get the
following:

Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'zstblSECMethod', database
'Lab_Data', schema 'dbo'.

I know I must be doing something bonehead stupid, but everything looks
find when I look at the permissions via the Mgmt Studio interface.
What am I missing?

Thanks in advance
Cheryl


.