locked
sql user login permission RRS feed

  • Question

  • Hi all,

    i have one database i want to implement secure login for sql having some right..

    1.user can not update data in object explorer.(like on table -> edit 200 rows option.

    2.But he can update data using query.

    for achevie this what role and right need to be assigned. 

    I have assigned role db_datareader and db_writer but user can edit table threw edit mode(edit 200 rows).





    Friday, February 13, 2015 6:07 AM

Answers

  • As Olaf says, for SQL Server there is no difference: it sees an UPDATE statement, and does not know from where it comes.

    However, I notice that the function app_name() returns different values depending on you are in a query window or use Edit TOP 200 rows, so maybe you have a trigger in place. But in this case, you would such a trigger on every table.

    Furhermore, since the application name is something you can set in the connection string, it is not going to stop a user who is dead set on it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, February 13, 2015 8:08 AM
  • In order to achieve this, you need to create stored procedures for data access.

    This should be your general approach.

    Then you will have for example one procedure schema.upd_tablename with an update command and a where-condition, that goes to the primary key or whatever other ranges you want to allow. The user you would put into a custom database role with Execute permission on the schema where those procedures reside. If you would allow update permission onto the table, the user could update even the whole table at once. So limiting CRUD-operations to stored procedures (or custom frontend with user-switch) really is key.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Friday, February 13, 2015 8:53 AM

All replies

  • 1.user can not update data in object explorer.(like on table -> edit 200 rows option.

    2.But he can update data using query.


    That's the same, SSMS also only sends an UPDATE command to SQL Server, so you can achieve this as security.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, February 13, 2015 7:18 AM
  • I have one existing user on server that's have functinality like

    we can update on table using but we can not update table data using SSMS (edit option).

    but i want to create same functiality user on another server.

    I have create user with same role and right but not able to achieve only this functionlity. 

    Friday, February 13, 2015 7:30 AM
  • I have one existing user on server that's have functinality like

    So, is he not seeing that option (edit top 200 rows) OR Getting error while editing?

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, February 13, 2015 7:57 AM
  • As Olaf says, for SQL Server there is no difference: it sees an UPDATE statement, and does not know from where it comes.

    However, I notice that the function app_name() returns different values depending on you are in a query window or use Edit TOP 200 rows, so maybe you have a trigger in place. But in this case, you would such a trigger on every table.

    Furhermore, since the application name is something you can set in the connection string, it is not going to stop a user who is dead set on it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, February 13, 2015 8:08 AM
  • In order to achieve this, you need to create stored procedures for data access.

    This should be your general approach.

    Then you will have for example one procedure schema.upd_tablename with an update command and a where-condition, that goes to the primary key or whatever other ranges you want to allow. The user you would put into a custom database role with Execute permission on the schema where those procedures reside. If you would allow update permission onto the table, the user could update even the whole table at once. So limiting CRUD-operations to stored procedures (or custom frontend with user-switch) really is key.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Friday, February 13, 2015 8:53 AM
  • Thanks to all for replay,

    Balmukund Lakhani getting error when going to edit with ssms. 

    Friday, February 13, 2015 10:19 AM
  • And which error is the user getting?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, February 13, 2015 10:29 AM