Identity Insert

[mikewin86]

Dear All,

May I ask you a question.

I have got bottle neck in my application.

My vb.net program read Product info from one database and insert to another database.

I wanna to be same ProductID for all databases.

So I used Identity Insert statement in my code and it run properly with sa (system admin role) but it cannot work with other user who is not in system admin role.

Please give me suggestion for this issue.

Thanks in advance.

Here is my code for it.

Dim objCmd As SqlCommand
Dim objDataAdapter As New SqlDataAdapter
Dim objReader As SqlDataReader
Dim strSQL As String
Dim con As SqlClient.SqlConnection
Dim adapter As SqlClient.SqlDataAdapter
Dim OtherConnection As String
 
OtherConnection = "Data Source=. ;Initial Catalog=MainSaleData;user id=mike;password=mike227;Connect Timeout=360"
 
 
strSQL = "SET IDENTITY_INSERT [Products]ON INSERT INTO Products (ProductID, [Product Code], ProductName   VALUES  ( '" & ProductID & "', '" & pcode & "', '" & ProductName & " ) " & "   SET IDENTITY_INSERT [Products]OFF "
 
con = New SqlClient.SqlConnection(str)
con.ConnectionString = OtherConnection
con.Open()
objCmd = New SqlCommand(strSQL, con)
objDataAdapter.InsertCommand = objCmd
objReader = objCmd.ExecuteReader
 

Mike

[Joseph Martell]

This sounds like you have a permissions problem with the user you are using to run the query. I have seen some companies in the past create an "applicatio n user id" which is just a user account that is created for, and used by, a specific application. This application user id is then given the required permissions for the target database, file access, etc. This avoids needing to elevate a normal user to an administrator role, which most SQL Admins and network admins do not like to do.

The other option is to talk to your SQL Admin (or do it yourself, if you have that privilege) to change the permissions of your table so it does not take an administrator to use Identity_Insert .

[mikewin86]

Dear Joseph,


Thanks for your suggestion.

I have already talked to SQL Admin for it but they do not agree and do not want to modify back end.

So I have got headache to solve it from my program.


Mike