Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to create user that can login to create and edit but cannot delete? Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 7:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37
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.




Post #1431324
Posted Thursday, March 14, 2013 11:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1431356
Posted Saturday, March 16, 2013 3:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 11:35 AM
Points: 49, Visits: 168
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


Post #1431873
Posted Saturday, March 16, 2013 12:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 5,951, Visits: 12,825
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"
Post #1431936
Posted Saturday, March 16, 2013 1:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37
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



Post #1431937
Posted Saturday, March 16, 2013 1:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37
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.



Post #1431939
Posted Saturday, March 16, 2013 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 5,951, Visits: 12,825
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"
Post #1431940
Posted Saturday, March 16, 2013 6:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37
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?



Post #1431951
Posted Saturday, March 16, 2013 6:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37
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?



Post #1431952
Posted Saturday, March 16, 2013 6:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37
// 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.



Post #1431953
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse