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.

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.

Perry Whittle (3/16/2013)

---

insert and update all tables or a handful?

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

clintonG (3/16/2013)

---

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

clintonG (3/16/2013)

---

including disallowing deleting the database itself.

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

clintonG (3/16/2013)

---

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?

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?

: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