how to create user that can login to create and edit but cannot delete?

  • Circumstances require a user that can be restricted to login to a specific database to create and edit data entered into tables but cannot delete anything. I presume this type of security has been around awhile but I could not find anything searching around so I ask is it possible and if so I need to learn how.

    I may also need some help with the T-SQL because the database is being generated by script that was generated by a tool and I will have to modify that generated sql script to create the user and login.

  • clintonG (3/14/2013)


    Circumstances require a user that can be restricted to login to a specific database to create and edit data entered into tables but cannot delete anything. I presume this type of security has been around awhile but I could not find anything searching around so I ask is it possible and if so I need to learn how.

    I may also need some help with the T-SQL because the database is being generated by script that was generated by a tool and I will have to modify that generated sql script to create the user and login.

    Take a look at GRANT and DENY in Books Online (press the {f1} key in SSMS to get there) and also have a look at all the different permissions that can be used in the links included in those two BOL articles.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just right click on the database and take permission tab. here u can assign the user for permissions.

    http://msdn.microsoft.com/en-IN/library/ms178569.aspx

  • clintonG (3/14/2013)


    Circumstances require a user that can be restricted to login to a specific database to create and edit data entered into tables but cannot delete anything. I presume this type of security has been around awhile but I could not find anything searching around so I ask is it possible and if so I need to learn how.

    I may also need some help with the T-SQL because the database is being generated by script that was generated by a tool and I will have to modify that generated sql script to create the user and login.

    insert and update all tables or a handful?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Here is one solution perhaps the best I've learned so far:

    // Transact-SQL

    REVOKE privilege_name

    ON object_name

    FROM {user_name |PUBLIC |role_name}

    // Example

    REVOKE DELETE

    ON TransactionRecordsTable

    FROM employee

  • Perry Whittle (3/16/2013)


    clintonG (3/14/2013)


    Circumstances require a user that can be restricted to login to a specific database to create and edit data entered into tables but cannot delete anything. I presume this type of security has been around awhile but I could not find anything searching around so I ask is it possible and if so I need to learn how.

    I may also need some help with the T-SQL because the database is being generated by script that was generated by a tool and I will have to modify that generated sql script to create the user and login.

    insert and update all tables or a handful?

    Disallow a specific User type the permission to DELETE any type of object in a specific database including disallowing deleting the database itself.

    This is a project for small businesses like pawnbrokers and second-hand resellers to record transactions they are required to report to police. I intend to disallow DELETE for what I hope is now an obvious reason. I think we call it "cover my @ss lol.

  • clintonG (3/16/2013)


    Here is one solution perhaps the best I've learned so far:

    // Transact-SQL

    REVOKE privilege_name

    ON object_name

    FROM {user_name |PUBLIC |role_name}

    // Example

    REVOKE DELETE

    ON TransactionRecordsTable

    FROM employee

    REVOKE only clears the granted permission, a user may still obtain this via another role,etc.

    clintonG (3/16/2013)


    Disallow a specific User type the permission to DELETE any type of object in a specific database

    You could grant the user insert and update permission on the schema the objects reside in.

    clintonG (3/16/2013)


    including disallowing deleting the database itself.

    No need to fear here as this requires a high level of elevated privilege. The user must have at least CONTROL on the database or be a member of DB_Owner role or SYSADMIN role, as long as you're not granting this then that's fine.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • You could grant the user insert and update permission on the schema the objects reside in.

    I was going to try it this way:

    EXEC sp_addrolemember db_datareader, $(DatabaseUserName)

    EXEC sp_addrolemember db_datawriter, $(DatabaseUserName)

    REVOKE DELETE

    ON $(DatabaseName)

    FROM {$(DatabaseUserName)}

    * Is the syntax correct?

    * Is there anything problematic with this approach?

  • clintonG (3/16/2013)


    You could grant the user insert and update permission on the schema the objects reside in.

    I was going to try it this way:

    EXEC sp_addrolemember db_datareader, $(DatabaseUserName)

    EXEC sp_addrolemember db_datawriter, $(DatabaseUserName)

    REVOKE DELETE

    ON $(DatabaseName)

    FROM {$(DatabaseUserName)}

    * Is the syntax correct?

    * Is there anything problematic with this approach?

  • // FAILED:

    EXEC sp_addrolemember db_datareader, $(DatabaseUserName)

    EXEC sp_addrolemember db_datawriter, $(DatabaseUserName)

    REVOKE DELETE

    ON $(DatabaseName)

    FROM {$(DatabaseUserName)}

    * Is the syntax correct?

    * Is there anything problematic with this approach?

    When I ran CreateUser.sql that failed with this message:

    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    // CreateUser.sql

    :setvar DatabaseName "PasswordVault"

    :setvar DatabaseUserName "csg"

    :setvar DatabaseUserPassword "csg"

    GO

    USE [$(DatabaseName)]

    DECLARE @usercount int

    SELECT @usercount=COUNT(name) FROM sys.database_principals WHERE name = '$(DatabaseUserName)'

    IF @usercount = 0

    CREATE USER $(DatabaseUserName) FOR LOGIN $(DatabaseUserName)

    GO

    EXEC sp_addrolemember db_datareader, $(DatabaseUserName)

    EXEC sp_addrolemember db_datawriter, $(DatabaseUserName)

    REVOKE DELETE

    ON $(DatabaseName)

    FROM {$(DatabaseUserName)}

    GO

    NOTE: no previous login or user named 'csg' existed before I ran scripts. Another script actually generates the database and the database was created as expected.

    Neither a Login nor User named csg was created by CreateUser.sql.

    Apparently CreateUser.sql bombed out because it tried to REVOKE DELETE on something not allowed but I can't determine what at the moment.

  • after granting the roles you would need to DENY delete not REVOKE

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Or, instead of using the roles, you could GRANT SELECT, INSERT, UPDATE on the database instead.

    Make sure that the login is not sysadmin, the user is not db_owner and you're not logged in as that user. All of those will cause the permissions to fail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/17/2013)


    Or, instead of using the roles, you could GRANT SELECT, INSERT, UPDATE on the database instead.

    Make sure that the login is not sysadmin, the user is not db_owner and you're not logged in as that user. All of those will cause the permissions to fail.

    Yup, already been down this route although I initially recommended insert and update at schema level only.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply