Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Sql server User permisssion

    hi all,


    i m working on sql server user permission i hv created sql server authentication user i need this user as read only can do

    1)only execute procedure but not able to view procedure text
    2)can only select,insert,update delete from tables but not able to view table design

    what permission should i give for this user?

    thx

  2. #2
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Check out the link below..

    http://www.google.co.in/url?sa=t&sou...hWl97w&cad=rja

  3. #3
    Join Date
    Sep 2011
    Posts
    71
    Hello ,i will take first part only of your question

    Stored Procedure Execution

    Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
    So is it useful for you?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You are probably going to end up grant execute as Paul mentions, but denying VIEW DEFINITION on these objects. Some testing will have to be done to make sure that works, though.

  5. #5
    Join Date
    Sep 2011
    Posts
    71
    Hello ,Regarding to second part of your question

    So, you could GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE::<database name> TO <principal>. Which is exactly equivalent to adding them to db_datareader and db_datawriter. However, it has the added flexibility of allowing you to do things like granting the ability to insert and update, without also giving them delete authority.
    So, you could GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE::<database name> TO <principal>. Which is exactly equivalent to adding them to db_datareader and db_datawriter. However, it has the added flexibility of allowing you to do things like granting the ability to insert and update, without also giving them delete authority.
    For more details you can see
    Grant SELECT, INSERT, UPDATE, DELETE on all table

  6. #6
    Join Date
    Nov 2011
    Posts
    21
    2)can only select,insert,update delete from tables but not able to view table design



    Yon need to deny View defination permission on Table

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •