Post reply

MS SQL SERVER 2000 - SQL USER RELATED QUESTION

  • January 1, 2005 at 2:23 pm

    #86192

    Hello ppl,

    I was wondering what permissions an sql user needs to execute the IDENTITY_INSERT command on a table (and can you show me how to do it?)

    Thanks

  • January 2, 2005 at 11:14 am

    #535566

    Just insert permissions

    set identity_insert mytable on

    insert mytable (myidentity, myvalue) select @myid, @myval

    set identity_insert off

  • January 3, 2005 at 12:13 am

    #535577

    Can I give (and if yes how?) only INSERT pesmissions to a user?

    I mean I only want the user to be able to INSERT without being a sysadmin or db_owner

    Thanks 4 ur help

    edit: The current user permission in the database is public

  • January 3, 2005 at 12:20 pm

    #535649

    You have to be db_owner to grant the rights, but you can give the user the right to insert without granting db_owner rights.

    GRANT INSERT ON tablename

    TO username (appears in your case this is public)



    Michelle

  • January 3, 2005 at 12:48 pm

    #535653

    once I grant INSERT rights to the user he can permanently use the IDENTITY_INSERT command? or I have to do this everytime? Is there any way to give rights for IDENTITY_INSERT to that table for that user permanently?

    Thanks alot

  • January 3, 2005 at 12:56 pm

    #535654

    The problem with Identity_Insert is it can only be set on one table in a database at a time. So if you

    SET identity_insert tableA ON

    then will not be able to

    SET identity_insert tableB ON

    until

    SET identity_insert tableA OFF

    is run.

    Check out the 'Set Identity_Insert' in Books Online on your SQL Server for more information.



    Michelle

  • January 3, 2005 at 1:15 pm

    #535657

    Yes the user will be able to do this permanently as long as he has inserts rights on the table.

    Obviously you need to grant access to all tables where the user needs to do the inserts.

  • January 3, 2005 at 5:10 pm

    #535685

    I granted access to the sql user on that table. Then I connected to the sql with that user and tried to SET identity_insert on that table to ON but still I get the access right refuse msg

    I granted access using sa to sql login and to public

  • January 4, 2005 at 6:23 am

    #535749

    what script did you use?

  • January 4, 2005 at 7:30 am

    #535773

    what do you mean? I've opened MS query analyzer and run GRANT INSERT ON MYTABLE TO webuser

    I use this login (webuser) for executing queries from a php website and I don't want to give db_owner access to that login to avoid injections. so I gave the webuser public access, but IDENTITY_INSERT is required for some tasks this is my problem that I don't know how to give only IDENTITY_INSERT access without giving any other permissions (like db_owner or sysadmin)

    Million thanks for your help

  • January 4, 2005 at 7:53 am

    #535780

    I've just read the books online and you need pretty high rights to execute this command (db_owner or db_ddladmin), contrary to what Steve suggested.

    Maybe you can write a stored proc to do the insert owned by dbo and execute that sp instead. Maybe that would work.

  • January 6, 2005 at 9:02 pm

    #536324

    can you give me an example how to create and execute that sp pls?

    also the webuser is connecting from a remote computer so with RPC off (I don't know if this has anything to do with executing sp).

    Thanks once again

  • January 7, 2005 at 6:17 am

    #536373

    RPC means remote procedure call. I don't know if it has anything to do with the RPC event in the profiler that is raised when you call a stored proc but here's how to find out :

    run this in query analyser :

    Create procedure dbo.Test

    AS

    Select 1

    GO

    then from your webpage try to execute this statement

    Dim Myrs (adodb.recordset)

    set myrs = MyConnection.Execute ("EXEC dbo.Test")

    If your receive a resultset from this then you can make and use a stored proc on the server. The SP would look something like this

    CREATE PROCEDURE dbo.MyProc @pk as int, @Field1 as int

    AS

    SET NOCOUNT ON

    SET identity_insert dbo.tableA ON

    Insert into dbo.TableA (Pk, Field1) values (@PK, @Field1)

    SET identity_insert dbo.tableA OFF

    SET NOCOUNT OFF

    GO

    please note that I used ""DBO"".TableA on all instances.

    Then you could call the stored proc like in the test with ("EXEC dbo.MyProc")

    P.S. Don't forget to give execute permission to webuser on the stored proc (GRANT EXECUTE ON dbo.MyProc TO WebUser)

  • January 8, 2005 at 8:59 am

    #536566

    after granting access for the sp to the webuser and doing all the above I still get the same error when trying to execute the sp via webuser:

    Server: Msg 8104, Level 16, State 1, Procedure SP_Register, Line 3

    The current user is not the database or object owner of table 'dbo.MYTABLE'. Cannot perform SET operation.

  • January 8, 2005 at 10:34 pm

    #536577

    I have no other idea taht can help you. Maybe some of the gurus can find an answer to your problem.

Viewing 15 posts - 1 through 15 (of 15 total)

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