Solved

SQL security permissions to insert with identity

Posted on 2010-09-20
26
2,120 Views
Last Modified: 2012-05-10
"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()"
0
Comment
Question by:tonygallo10
  • 9
  • 6
  • 4
  • +3
26 Comments
 
LVL 4

Expert Comment

by:rstjean
ID: 33721039
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
 
LVL 4

Expert Comment

by:rstjean
ID: 33721048
Err ... the identity insert is off.  Turn it on, insert, then turn it off, so it will fire automatically.
0
 

Author Comment

by:tonygallo10
ID: 33721301
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
 
LVL 4

Expert Comment

by:rstjean
ID: 33721411
Does your account have have ownership of the database you are trying to access or just read, delete, and insert?
0
 

Author Comment

by:tonygallo10
ID: 33721426
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
 
LVL 4

Expert Comment

by:rstjean
ID: 33721708
Add the user to the db_ddladmin role.
0
 
LVL 4

Expert Comment

by:rstjean
ID: 33721718
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
 
LVL 4

Expert Comment

by:timexist
ID: 33722441
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
 

Author Comment

by:tonygallo10
ID: 33729514
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
 
LVL 4

Expert Comment

by:rstjean
ID: 33729567
You did make the sp owned by dbo right?
0
 

Author Comment

by:tonygallo10
ID: 33729998
Yes. When I look at the sp properties, the Schema = dbo
0
 

Author Comment

by:tonygallo10
ID: 33730171
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:tonygallo10
ID: 33730233
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
 
LVL 15

Accepted Solution

by:
MohammedU earned 167 total points
ID: 33738172
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
 

Author Comment

by:tonygallo10
ID: 33738794
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33739048
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33739095
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
 

Author Comment

by:tonygallo10
ID: 33739303
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33739534
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 166 total points
ID: 33739545
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 167 total points
ID: 33739548
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
 

Author Closing Comment

by:tonygallo10
ID: 33739731
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33739736
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33739751
And of course, always worthwhile checking DENY...

Well done team, great result, and thanks tonygallo10
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now