locked
limit user rights SSMS security access RRS feed

  • Question

  • I created a limit users using SSMS.

    The user only have rights to execute stored procedure.

    I use limit users to login using SSMS and realized that the limit user can access tables, stored procedures.

    I tried to modeify the tables using limite user login, SSMS shows warning message that user can not modify the tables, since it is not the owner.

    I just wanted to confirm that with limit rights user ID still can see table design, data and source code of the stored procedures using SSMS

    If it is true then we should not give any users SSMS to access tables and stored procedures.

    Please let me know if I am wrong.

    I thought that limit rights user login ID only can excute stored procedures and can not see the tables.

    Your help and information is great appreciated,

    Regards,

    Souris,

    Tuesday, April 2, 2013 6:38 PM

Answers

  • Hi there,

    by default any login will have public access. means they can see inside your database. If you want to restrict the users do the following steps.

    1) add the user and leave the server role to public.

    2) map the database and select db_denydatareader

    3) go back to the database security select the user properties>> securables>> add the procs that you wanted to give execute permission and select execute.

    or simple run the query

    use [db_name]
    GO
    GRANT EXECUTE ON [schema].[procname] TO [username]
    GO

    Good luck

    kumar

    • Marked as answer by sourises Tuesday, April 9, 2013 6:13 PM
    Wednesday, April 3, 2013 12:20 AM
  • Yes, the user can right-click the Table node in Object Explorer and select New Table. However, the user should not able to save anything. Nor should the user be able to see any existing tables in Object Explorer.

    If you grant user EXECUTE on stored procedures, that implies VIEW DEFINITION. I though that meant that the user could see the source code, but when I test I find that my low-priv test user only can see that the procedure exists, but it is not able to see the source code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by sourises Tuesday, April 9, 2013 6:14 PM
    Wednesday, April 3, 2013 9:24 PM

All replies

  • A new fresh user has no permissions beyond what you get from the public role. And that is not much. Certainly not access to tables, etc. Unless you have explicitly granted that to public.

    You are not saying whether your limitted user is an SQL login or a Windows login. An SQL login is easier to deal with, since fresh it is not member of any roles. But a Windows login may be member of AD groups to which more extensive permissions have already been granted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 2, 2013 9:29 PM
  • Hi there,

    by default any login will have public access. means they can see inside your database. If you want to restrict the users do the following steps.

    1) add the user and leave the server role to public.

    2) map the database and select db_denydatareader

    3) go back to the database security select the user properties>> securables>> add the procs that you wanted to give execute permission and select execute.

    or simple run the query

    use [db_name]
    GO
    GRANT EXECUTE ON [schema].[procname] TO [username]
    GO

    Good luck

    kumar

    • Marked as answer by sourises Tuesday, April 9, 2013 6:13 PM
    Wednesday, April 3, 2013 12:20 AM
  • Thanks for the message and helping,

    It is SQL login which is a member of a role.

    Yes, I grant public to the user.

    Can public rights to access tables design view?

    The database role has rights to execute stored procedures.

    if this is the case should the limit users to access tables design view and view stored procedures source code?

    Thanks again for helping,

    Regards,

    Souris,


    • Edited by sourises Wednesday, April 3, 2013 12:26 AM
    Wednesday, April 3, 2013 12:24 AM
  • Yes, the user can right-click the Table node in Object Explorer and select New Table. However, the user should not able to save anything. Nor should the user be able to see any existing tables in Object Explorer.

    If you grant user EXECUTE on stored procedures, that implies VIEW DEFINITION. I though that meant that the user could see the source code, but when I test I find that my low-priv test user only can see that the procedure exists, but it is not able to see the source code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by sourises Tuesday, April 9, 2013 6:14 PM
    Wednesday, April 3, 2013 9:24 PM
  • Thanks a million for helpig and information,

    Regards.

    Souris,

    Tuesday, April 9, 2013 6:15 PM