Post reply

Issue with Identity_Insert

  • October 25, 2004 at 3:26 pm

    #86774

    Hay everyone thanks for all the great info so far, its really helped out this newbie DBA. I've come across an issue with SET IDENTITY_INSERT.

    We've recently moved our data center and reworked server logins. We changed "Joe"'s permissions from sysadmin to datareader, datawriter on all databases. Well some of our code still wants to use the sysadmin permissions to run "SET IDENTITY_INSERT that.dbo.thing ON". Can't give "Joe" sysadmin privlages again, so the question comes up, is there any way to allow "Joe" to run this command without blowing security out of the water?

    One thought that has been brought up is to set up another login, which is possible for now (don't know if its the best way), but we are moving code from ColdFussion (in house) to .NET (third party), and I really don't want the third party to have certain permissions, for obvious reasons.

    If I've done a good job of confusing everyone, let me know and I try and clear it up.

    Thanks in advance for the time and help.

  • October 25, 2004 at 3:33 pm

    #527881

    What are you using IDENTITY_INSERT for? Why not just let the IDENTITY columns increment themselves?

    --
    Adam Machanic
    whoisactive

  • October 25, 2004 at 3:45 pm

    #527884

    The database contains users with user ID info that is 10 digits long, well someone in the past thought is was a good idea to set up a user for a diffrent site with diffrent length ID and use the same data base. So user 1 a has ID = 1000000001 which auto increments, but we have a process that creates a new user ID and needs to insert user ID = 90000001 into table that.dbo.thing.

    Don't know why it was set up that way, but thats what I got stuck with for now (I hope for now).

  • October 25, 2004 at 4:09 pm

    #527885

    Looks like it's time to change that

    IDENTITY_INSERT is intended for sysadmin use when something goes wrong; I don't think there's a workaround to the hack that your predecessors put into place.

    --
    Adam Machanic
    whoisactive

  • October 25, 2004 at 4:29 pm

    #527886

    I was afraid that was the type of answer I was gonna get. I've got all kinds of hacks to deal with, fun for a new DBA.

    Thanks for the time Adam.

  • October 25, 2004 at 4:46 pm

    #527887

    Maybe you should change your website to "lucky13" instead?

    --
    Adam Machanic
    whoisactive

Viewing 6 posts - 1 through 5 (of 5 total)

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