Giving a user permissions on objects in a schema

Hot Topic

SQL Server 2000
Calculated measure based on field in fact table that is not a measure in cube.
IUSER Security Risk??
How to update Metadata data types without editing XML code
DBTYPE of 130 at compile time and 5 at run time
SQL Server vs Oracle
How to find out the PK from a table?
Is this SQL requirement possible?
disable implicit casting
Report parameter question

SQL Server

can i restore sql 2000 from veritas under sql 2005?
Can SSRS 2005 Handle Stored Procedures or SQL Subqueries that Rreturn Rowsets Based on Multiple SQL Updates?
Sql log can not increase?
Good design?
Client Side Redirect - Database Mirroring
How to Redistribute sqlExpress
Service Pack 2 for SQL Server 2005 is avaliable!
help with an expression
Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server
Changing "Connection String" in SSIS package ???

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.

 




Answer this question

Giving a user permissions on objects in a schema

  • KevinBurton

    A ROLE is a PRINCIPAL type which doesn't need any owner. A SECURABLE needs an owner

  • langev

    Hemantgiri S. Goswami wrote:

    Hi,

    In General we do

    ...

    Good stuff. Thank you very much.



  • PeterVrenken

    Hemantgiri,

    I have one more question around this.

    • I have created a schema MySchema.
    • MySchema is owned by a database role db_MyRole.
    • I can add users into that role.

    This all works great. So one question....

    Who should be the owner of db_MyRole

    Thanks

    Jamie



  • MattHei

    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"



  • Giving a user permissions on objects in a schema
Answer this question