Giving a user permissions on objects in a schema

Hi,

SQL Server Security is not my strong point so forgive me for asking stupid questions.

I have a bunch of tables and sprocs within a schema 'MySchema'. I have a user 'MyUser' defined in the database.

I would like to give MyUser permission to SELECT from tables and EXECUTE all sprocs in MySchema. What is the simplest way of doing that? Will the following:

GRANT EXECUTE ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION AS [db_owner]
GRANT SELECT ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION

accomplish that? (I can't test it out at the moment because our DBA isn't around and I don't have permission)

With best practices in mind - is what I am doing here considered "ok". Any suggestions/comments are welcome.

-Jamie

P.S. Can anyone recommend any documentation that talks about what best practices should be in the use of schemas. BOL is a bit sparse. Thanks.

Hi,

In General we do

1). Create a Role

2). Assign a proper permission/privilege using Grant as you describes

3). Create user/group

4). Map users/user group to earlier create Role

Best Practise describes

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Here is a Check List for Server Security

http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx

"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans"

|||

Hemantgiri S. Goswami wrote:

Hi,

In General we do

...

Good stuff. Thank you very much.

|||

Hemantgiri,

I have one more question around this.

I have created a schema MySchema.|||A ROLE is a PRINCIPAL type which doesn't need any owner. A SECURABLE needs an owner