SQL security permissions to insert with identity
Posted on 2010-09-20
"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;
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()"