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

Question

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

clintonG

Right there with Babe

Points: 741
More actions
March 14, 2013 at 7:45 pm

#291063

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.

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

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)

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 2

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

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" 😉

clintonG Right there with Babe Points: 741 More actions · Answer 4

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 Right there with Babe Points: 741 More actions · Answer 5

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.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

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" 😉

clintonG Right there with Babe Points: 741 More actions · Answer 7

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 Right there with Babe Points: 741 More actions · Answer 8

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?

clintonG Right there with Babe Points: 741 More actions · Answer 9

// 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.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

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" 😉