DBA Won't Allow Alter Table for Identity Insert

Question

DBA Won't Allow Alter Table for Identity Insert

Mike Voissem

SSC-Addicted

Points: 435
More actions
March 12, 2010 at 2:13 pm

#230769

I'm trying to use SSIS to bulk load a table that has an identity column, and for this instance the ssis package will be loading records with a value for the identity column already. I choose "Keep Identity" on the OLE DB Destination, but the DBA states they won't give Alter Table permissions to the users. Is there any way that we can accomplish this? We will also have instances where we'll want to drop a value in an identity column through SSIS when we're loading child records as well. It would be nice if we could accomplish this without having to get into a heated discussion with the DBA's.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I believe that "Keep Identity" is effectively SET IDENTITY_INSERT for a table, which per books online:

User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.

Who will be executing the task in production? A user account or will it be run under the context of the SQL Server itself.

Typically DBAs will not allow this, I tend to agree.

CEWII

Mike Voissem SSC-Addicted Points: 435 More actions · Answer 2

The people running the SSIS will be users. I'm thinking that my best way for this is to create an agent job to run the SSIS, and hopefully the service account will be granted rights to alter the table for such purposes. It would be nice to be able to just grant alter on that column.

Thanx for the reply:-)

LutzM SSC Guru Points: 107049 More actions · Answer 3

One option would be to set up a staging table you could use for your bulk load task and add a stored proc to insert those data into the final table. Would allow testing for valid data, too.

I'll second Elliot: the DBA is (most probably) right when refusing tho give that high level of permissions.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Mike Voissem (3/12/2010)
The people running the SSIS will be users. I'm thinking that my best way for this is to create an agent job to run the SSIS, and hopefully the service account will be granted rights to alter the table for such purposes. It would be nice to be able to just grant alter on that column.
Thanx for the reply:-)

If it is run as "sa" then it is run under the context of the server itself, no permissions would need to be granted.

Also, I would really have to think about allowing users to run SSIS packages as themselves in a production environment. I'd have to see a heck of a business requirement and even then I don't think it would happen..

CEWII

LutzM SSC Guru Points: 107049 More actions · Answer 5

Elliott W (3/12/2010)

Also, I would really have to think about allowing users to run SSIS packages as themselves in a production environment. I'd have to see a heck of a business requirement and even then I don't think it would happen..
CEWII

I can't think of any such requirement leading to SSIS as the only option either.

Even if there would be tasks that cannot be done using stored procedures (e.g. uploading a file from client to server), the only thing that would be left for SSIS to do is to get the data to a point where stored procedures would take over (e.g. staging table or shared drive to continue the example).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GTR SSCrazy Eights Points: 8007 More actions · Answer 6

I am with Elliot and Lutz on this, Standard protocol is to build a SSIS package and test that in Dev environment and give package to DBA's in Prod, DBA's run the package on demand or create a job for the process.

EnjoY!

Mike Voissem SSC-Addicted Points: 435 More actions · Answer 7

If I don't map the Identity Column, shouldn't it just get populated then? In just testing this out, if I don't map to the Identity Column and un-check "Keep Identity", I still get an error stating I do not have permission, which is rather odd. All the other tables in that database I can load just fine. And there are 2 other tables in the same schema that don't have an identity column that load just fine.

As for the user running SSIS jobs. We have a lot of files come in from clients for mailings, orders, etc. that in the old mainframe days, the users would load the file to the mainframe and run jobs to create reports. they wanted that same capability today. The large majority of our work is ad-hoc, and probably only 20% of our data keeps getting added to. The rest is somewhat transient, usually project specific and then it's gone. Very little accruing of data is taking place.

GTR SSCrazy Eights Points: 8007 More actions · Answer 8

Are you doing this in production? Did you test this in Integration?

EnjoY!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

What is the nature of the data where you'd want to insert directly into an IDENTITY column?

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Voissem SSC-Addicted Points: 435 More actions · Answer 10

Old Hand, yes this was tested in test and it worked there. Of course I've got more rights there than I do in prod.

Jeff, sometimes the data we get comes from a client with the Identity column already populated (so we can tie the parent and child records together), and we need to maintain that value when we export the data and send back to the client. I can understand the who part about the DBA or Job's controlling the running of SSIS packages, except in these cases where so much of this is ad-hoc and our client service people need to turn things around pretty quickly.

In order to resolve, I'm just going to do as suggested and create the staging tables, and add an additional table to house the "Identity" should a client send one, so I can correlate the clients Identity to ours. That would probably cause less friction with the DBA's, and I'd be able to get things accomplished faster.

Thanx all for your input, it gives me a better perspective of how things should be.:hehe:

david_wendelken SSCrazy Points: 2388 More actions · Answer 11

Trying to keep the CLIENT's identity value in YOUR identity column is a mistake.

Client A sends records with identities of 1-500.

Client B sends records with identities of 1-500.

Both want their identity values kept.

You can't have them both!

You should just let the identity column be what it is, a unique, meaningless id for the data in YOUR copy of the table.

If you want to maintain a copy of the client's ID value, you should add a column to the table called "ClientID" and populate it with what you want.

You might want to have it default to your identity value if you don't specify a client id value.

Mike Voissem SSC-Addicted Points: 435 More actions · Answer 12

Thanx, this is the route I've chosen to take. Another point I failed to consider was that even if I used a unique client identifier to segregate clients, there's still the possibility that the client would send a duplicate record that could cause an error as well.