January 1, 2005 at 2:23 pm
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
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
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
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
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
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
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
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
what script did you use?
January 4, 2005 at 7:30 am
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
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
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
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
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
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