Re: sql2k5 security

Hi Param,

As I have mentioned, you could give a user only Execute permissions on a
stored procedure and the stored procedure could perform operations fine.

Here is my steps:

1. Create a table (tbl_Test ) in the database.

2. Create a Schema named mySchema in the database.

3. Create a database role named db_web.

4. Create a user named webUser and add it to the db_web role. The default
schema of this user is setting to mySchema.

5. Create a stored procedure named myShcema.doSelect.

The content of this proc is:

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
select * from dbo.tbl_test
END

6. Grant the execute permission of myshcema to db_web role.

7. Login the server with user webUser.

8. Try to execute the stored procedure, it runs successfully and return the
right result.

9. If try to select the result from the tbl_test directly, it will get the
following error:

SELECT permission denied on object 'tbl_Test', database 'Test', schema
'dbo'.

I think this is the behavior you wanted. Please try these steps on your
side and let me know the result. If you have anything unclear, please feel
free to let me know.

Also, you could post the scripts you use here for my troubleshotting. Thank
you!

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

.

Relevant Pages

  • Re: sql2k5 security
    ... you could give a user only Execute permissions on a ... stored procedure and the stored procedure could perform operations fine. ... Create a table in the database. ... Create a Schema named mySchema in the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Using xp_smtp_sendmail from a stored proc
    ... the user database needs to be owned by 'sa' so that the ownership ... >>> application that the user needs execute permissions on the extended ... >>>>> legacy) for the extended stored procedure. ...
    (microsoft.public.sqlserver.security)
  • A philosophical question about inserts
    ... Is it better to let each developer create their own prepared SQL statements for inserts or to hide the schema from them and have them call stored procedures or views to get the data into the database? ... In the 2nd case, the schema is invisible to him/her but if a column was added to 'table', they would have to change their stored procedure call all the same. ...
    (comp.databases.ms-sqlserver)
  • Re: BizTalk 2004 SQL Adapter
    ... An incoming document is mapped to database stored procedures in the ... Because it has a header ... write the header and the detail is mapped to stored procedure to store ... But this doesn't look anything like the schema generated for a send ...
    (microsoft.public.biztalk.general)
  • Re: Using xp_smtp_sendmail from a stored proc
    ... application that the user needs execute permissions on the extended stored ... > Grant EXECUTE permission on the stored procedure that you wrote ... >> grant users the execute right on the extended stored proc. ...
    (microsoft.public.sqlserver.security)