the security needed for SET IDENTITY_INSERT
Roy Goldhammer
I have 2 databases: Db1 and DB2.
I have table on DB1 called Users and store procedure which insert data to
it: save_new_user_sp.
i also have procedure on db2 called: save_user_sp which calls to procedure
on db1 in:
exec DB1.dbo.SAVE_NEW_USER
the user who run the procedure save_user_sp have only permissions to run
these both procedures only.
however when i add the code: SET IDENTITY_INSERT Users ON run to
save_new_user_sp and run the procedure on db2 i'm getting the error: Cannot
find object Users.
which permission i need for SET IDENTITY_INSERT .. ON?
Jeroen Mostert
exist or you do not have permission to see it at all. This is a more basic
issue than the permission needed for SET IDENTITY_INSERT -- you can't
operate on objects you can't see, regardless of what the operation is.
In this case, giving someone permission to execute an SP doesn't
automatically give them permission to change the objects the SP is referring
to. You need to grant access to the table as well. If this is not desirable,
you can look into using the EXECUTE AS clause to execute the stored
procedure with elevated permissions without giving the user explicit
permissions on the table itself.
As Books Online specifies, to use IDENTITY_INSERT the 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." This is a lot of privilege, so you
probably don't want to do that and go for the EXECUTE AS option.
--
J.
Erland Sommarskog
> I have 2 databases: Db1 and DB2.
>
> I have table on DB1 called Users and store procedure which insert data to
> it: save_new_user_sp.
>
> i also have procedure on db2 called: save_user_sp which calls to procedure
> on db1 in:
> exec DB1.dbo.SAVE_NEW_USER
>
> the user who run the procedure save_user_sp have only permissions to run
> these both procedures only.
>
> however when i add the code: SET IDENTITY_INSERT Users ON run to
> save_new_user_sp and run the procedure on db2 i'm getting the error:
> Cannot find object Users.
Well, if you need to that in a procedure which is to be run by plain
users, you have messed up the database design, and should fix it in
my opinion.
But I guess that you need at least ALTER if not CONTROL on the table
to do it. You can easily find out by looking in Books Online: for each
command there is a permission section.
To actually use this in a procedure would need to sign it with a
certificate and create a user from that certificate and grant that
user the required privs. Then the user can run the procedure with
no rights on their own. I describe this method in detail in an
article on my web site: http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Roy Goldhammer
The reason i need the SET IDENTITY_INSERT .. ON for user id it because the
procedure on db2 first insert data to another Users table (in old system)
and to maintain the old and new system i must enter the same USER_ID on the
new system on db1.
as you guess i don't want to bring to WEB user (the user which runs the
procedure from the application) the less permissions i want to give to him.
and i would defenetly wont bring him sysadmin or DB_OWNER.
the exeute as does not working because when the application begins with web
the execute as login = 'admin' cannot be done (which is very good except
this case).
In the current structure of the work i can't support certificate in the
while.
which specific permissions i need to bring to USERS table on db1 for user
WEB to run SET IDENTITY_INSERT?
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9DDEDD82D...@127.0.0.1...
Erland Sommarskog
> The reason i need the SET IDENTITY_INSERT .. ON for user id it because
> the procedure on db2 first insert data to another Users table (in old
> system) and to maintain the old and new system i must enter the same
> USER_ID on the new system on db1.
If you did not have an IDENTITY column, you would not need SET
IDENTITY_INSERT.
> the exeute as does not working because when the application begins with
> web the execute as login = 'admin' cannot be done (which is very good
> except this case).
EXECUTE AS is not good for many reasons. I recommend certificate signing.
Did you look at my article?
> which specific permissions i need to bring to USERS table on db1 for user
> WEB to run SET IDENTITY_INSERT?
Did you read the permissons section in Books Online?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx