SQL Server 2005 Security

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

.

Relevant Pages

  • Re: SQL Server 2005 Security
    ... I forgot I had denied access to this table to the ... CREATE User ClrUser FOR LOGIN ... GRANT EXECUTE ON dbo.TestExecuteAs TO CLEARED (this stored procedure ... SELECT permission denied on object 'zstblSECMethod', ...
    (microsoft.public.sqlserver.security)
  • Solaris 10 autofs directory permissions - Solution
    ... the fact that my map file has 755 permissions not 644. ... If the execute permission is set, it becomes an executable map which is ... map is expected to return the content of an automounter map ...
    (SunManagers)
  • Re: Rights to execute jobs?
    ... Does anyone know if the rules surrounding stored procedure permissions apply ... Does this apply to a proc calling sp_start_job so that I ... raise the alert, so it could be anyone on the server. ... is that a group needs to not be an admin but still be able to execute ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie
    ... I will now tell you some basic commands. ... Easy file has rights and a owner. ... file and who may execute it. ... The last three characters are about the permissions of user ...
    (alt.linux)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)