SQL security permissions to insert with identity

"The INSERT permission was denied on the object 'SEQ_LPN'"
Why am I getting this error? What SQL rights are needed to insert with an IDENTITY column? Do I need to give access to some system tables?

The table is created as
CREATE TABLE [dbo].[SEQ_LPN](
	[sqVal] [int] IDENTITY(500000000,1) NOT NULL
) ON [PRIMARY]

In SQL Server 2008, I have granted all rights to the table. In other words, via SQL Management Studio, I right click on the table, select permissions, select the user DOMAIN\user and then click on all of the permissions (Alter, Control, Delete, Insert, References, Select, Update, View change tracking, View definition) except Take ownership.

Here is the statement, which causes the SQLException:
SqlCommand cmdNextLPN = new SqlCommand("INSERT INTO SEQ_LPN DEFAULT VALUES SET @lpn = SCOPE_IDENTITY()", cn);
SqlParameter paramNextLPN = new SqlParameter("@lpn", SqlDbType.Int);
paramNextLPN.Direction = ParameterDirection.Output;
cmdNextLPN.Parameters.Add(paramNextLPN);
cmdNextLPN.ExecuteNonQuery();

The statement is executed via C# ADO.NET, but I don't think it is a programming issue. Because if I log in via my developer account, everything works okay, because I have sysadmin rights to the database.

Note: The user can connect to the database and select from another table. The error occurs when "INSERT INTO SEQ_LPN DEFAULT VALUES SET @lpn = SCOPE_IDENTITY()"
tonygallo10Asked:
Who is Participating?
 
MohammedUConnect With a Mentor Commented:
Identity column is auto increment column, if you want to insert the specific value into identity column then you need to use the SET IDENTITY option....

Make sure there is no DENY insert on the table of the user or user is not member of db_denydatawriter group because DENY takes precidence over GRANT if there is any DENY setting there..
0
 
rstjeanCommented:
Your auto identity is on.. you need to turn it off... then run your insert, then turn it back on.

SET IDENTITY_INSERT TBL_Test ON
INSERT INTO TBL_test([Id], [Text], [Name], [Createddate]) VALUES (3, 'Testing', 'tester', date())
SET IDENTITY_INSERT TBL_test OFF
0
 
rstjeanCommented:
Err ... the identity insert is off.  Turn it on, insert, then turn it off, so it will fire automatically.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tonygallo10Author Commented:
Err ... did you notice the following in my original post?
I don't think it is a programming issue. Because if I log in via my developer account, everything works okay, because I have sysadmin rights to the database.
0
 
rstjeanCommented:
Does your account have have ownership of the database you are trying to access or just read, delete, and insert?
0
 
tonygallo10Author Commented:
Neither account owns the database. The developer account has public and sysadmin roles in the database. The user account does not. I am granting permissions at the table object level for the user account. The user account gets the error. The developer account works fine.
0
 
rstjeanCommented:
Add the user to the db_ddladmin role.
0
 
rstjeanCommented:
Sorry I hit enter too quickly.  The user will need dbo access as the set command will only allow the owner to run.

Another alternative is to write a stored procedure, make the DBO owner of the SP, but give execute rights to your user account.
0
 
timexistCommented:
I agree with rstjean, put it a sp is better.
as Microsoft suggested, always use stored procedure when it can.

create procedure stpSEQ_LPN
@lpn int
as
begin

SET IDENTITY_INSERT SEQ_LPN ON
INSERT INTO SEQ_LPN([sqVal]) VALUES (@lpn )
SET IDENTITY_INSERT SEQ_LPN OFF

end

0
 
tonygallo10Author Commented:
The sp may be better, but it does not solve the security problem. I have implemented the sp and gave the user Execute rights to the sp. When the user executes the sp, the SQL Exception is "The INSERT permision was denied on the object 'SEQ_LPN' ... while my developer account can execute the sp with no error.
0
 
rstjeanCommented:
You did make the sp owned by dbo right?
0
 
tonygallo10Author Commented:
Yes. When I look at the sp properties, the Schema = dbo
0
 
tonygallo10Author Commented:
Here is Microsoft documentation http://msdn.microsoft.com/en-us/library/ms188059.aspx

I do NOT need to insert identities out of sequence, so I am not sure why rstjean and timexist have recommended SET IDENTITY_INSERT ON/OFF to be used. Under Permissions section in documentation, Microsoft says "User must own the object, or be a member of the sysadmin fixed server role ..." This maybe the root cause, but I am still searching for a work around it.

0
 
tonygallo10Author Commented:
http://connect.microsoft.com/SQLServer/feedback/details/352973/permissions-required-to-run-set-identity-insert-seem-too-restrictive

this reference is for 2005, so perhaps 2008 has capability ... but in any case I do not need to skip or fill the sequence ... getting the next sequence value is good enough for this user ... this seems a typical need ... is it really this hard?
0
 
tonygallo10Author Commented:
I cannot find any DENY settings, but it seems like this is the problem. Is there a way to search for explicit deny settings.

I look at the Securables for this database user. There are two tables, which cause INSERT permission denied. As I select each of the tables and look at the corresponding Explicit permissions tab. GRANT is selected for Insert, Select, and Update. But when I click on the Effective tab, only SELECT is listed.

The user is a member of db_datareader and db_datawriter database roles, but none of the deny... roles.

0
 
Kevin CrossChief Technology OfficerCommented:
Might be a silly question, but have you tried simply granting INSERT permissions for the table(s) you are inserting to and checking if they have any triggers associated with them that are updating / inserting to tables that you don't have permissions to.  I would double check and ensure that the user without the sysadmin role has been granted explicit INSERT/UPDATE permissions on any table that it needs to have it on.

I might have missed where you said you did this in the chain as it appears you have some SQL knowledge already, so please forgive me.

And I see now that you said the user is a member of db_datawriter role, so I would still follow the triggers path above as possibly you have another database where you are auditing / logging changes like inserts to table to and the user needs to be granted INSERT rights to the audit table or db_datawriter role to the audit database.
0
 
Kevin CrossChief Technology OfficerCommented:
And double check that your login is still correctly mapped to the user on the database end...might be something screwy like that so when you check the user has proper permissions but the login isn't mapped to it so is getting public permissions instead.
0
 
tonygallo10Author Commented:
My SQL knowledge is from an ADO.NET programmer's perspective and not from that of a good DBA, so please offer any advice you think is appropriate.

The two tables are new to this database and were added by me. There are no triggers, which I am aware. Also, the Database Triggers folder is empty under database->Programmability->Database Triggers

I am clicking on the user in the security folder of the database. Does this mean the user is mapped correctly to the database?

Yes. I have explicitly granted Insert, Update, and Select permissions on each of the two table objects. It is strange when I change to the Effective tab, only SELECT permission is listed for each column in the table.
0
 
Kevin CrossChief Technology OfficerCommented:
Go to the Server level and check the Logins for the login that is supposed to be associated to the user in question.  When you are in properties for the login in question, click on User Mapping and ensure that the database in question is selected and that the login is connected with the user you think it is.
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
To SQL experts...

tonygallo10 is not trying to insert a value into the indentity column, he is trying to retrieve the identity value via an output param declared as @lpn int.

This is the equivalent in straight SQL :

CREATE TABLE [dbo].[SEQ_LPN]([sqVal] [int] IDENTITY(500000000,1) NOT NULL)

declare @lpn int

INSERT INTO SEQ_LPN DEFAULT VALUES SET @lpn = SCOPE_IDENTITY()

select @lpn

-- which is really TWO commands

INSERT INTO SEQ_LPN DEFAULT VALUES;
SET @lpn = SCOPE_IDENTITY()


Question for tonygallo10 - Why do it like that ? I can see that you want to gather the "unique value" but that is normally associated with a master of some description and inserted then have that value retrieved. Having a master of just the identitiy column is an interesting design. The other thing we sometimes see is a desire to first create a row, and then populate that row where as it is often the case that the row should be populated in the one swoop.

In terms of a SP, then it can also work using either an output param, or simply gather the result sets...

create procedure usp_get_next_seq_lpn (@lpn int OUTPUT)
as
begin
   set NOCOUNT on
   INSERT INTO SEQ_LPN DEFAULT VALUES;
   SET @lpn = SCOPE_IDENTITY()
end

-- and then once created, can use the SP

declare @new_lpn int

exec usp_get_next_seq_lpn @new_lpn OUTPUT

select @new_lpn

0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
And check what application roles the user belongs to make sure that there are no DENY permissions set on a group/role to which the user belongs.
0
 
tonygallo10Author Commented:
deny_datawriter was granted to an active directory group. This user was a member of that group. As MohammedU stated, DENY takes precedence over the explicit GRANT given to the user. I changed the roles and groups to prevent the overlap of DENY and GRANT permissions.
0
 
Mark WillsTopic AdvisorCommented:
So, in code... something like :


myCommand = New SqlCommand("usp_get_next_seq_lpn", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

workParam = myCommand.Parameters.Add("@lpn", SqlDbType.Int)
workParam.Direction = ParameterDirection.Output

myCommand.ExecuteNonQuery()

' then get / use the value from : myCommand.Parameters("@lpn").Value
0
 
Mark WillsTopic AdvisorCommented:
And of course, always worthwhile checking DENY...

Well done team, great result, and thanks tonygallo10
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.