Re: SET IDENTITY_INSERT Privileges?

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 08/27/03 

Date: Tue, 26 Aug 2003 17:58:52 -0600

AFAIK, there is no way around this. You can't grant
permissions on the set identity_insert nor can you wrap it
in a stored procedure and give the user execute permission
on the stored procedure to get around this.
It doesn't work like DML statements where when it's in a
stored procedure and the user has execute permissions on the
procedure and ownership chains are intact, the user can
execute the stored procedure and permissions on the
underlying objects aren't checked.
The user needs to be in one of the indicated roles (or the
object owner) to execute the set identity_insert.

-Sue

On Tue, 26 Aug 2003 16:33:28 -0700, "Satish Balusa"
<sbalusa_nospam@corillian.com> wrote:

>We have a "SET IDENTITY_INSERT " statement in a stored procedure and the
>stored procedure has been granted execution privileges to a user 'xyz'. This
>user 'xyz' is Neither a dbo Nor part of db_ddladmin. role. When the stored
>procedure is being executed I get a permission denied msg.
>
>>From BOL
>Execute permissions default to the sysadmin fixed server role, and the
>db_owner and db_ddladmin fixed database roles, and the object owner.
>
>So my question is : How do I grant permission to a SET Statement??
>
>Any help in this regrad is appreciated.
>TIA
>~Sat
>
>

Relevant Pages

  • Re: Logon failed for user ". but only for membership tables in same database that other request wor
    ... I was able to log on to computer b and execute the stored procedure. ... was no error after I corrected the permissions for the user. ... I can access the stored procedure but I can not use membership.validateuser. ... You may use the GRANT statement to grant the EXECUTE permission for a ...
    (microsoft.public.sqlserver.security)
  • Re: Executing dynamic select statement in a SP
    ... EXECUTE permissions for a stored procedure default to the owner of the ... the statementwithin the EXECUTE string are checked at the time EXECUTE ... > everything works just fine but with dynamic SQLs when I ...
    (microsoft.public.sqlserver.security)
  • With in a SP Truncate dbo.table table-name permissions..
    ... If i give execute permission to this ... truncate table permissions on the table. ... If a user who creates a stored procedure does ... not qualify the name of the tables referenced in SELECT, ...
    (microsoft.public.sqlserver.security)
  • Re: app role has exec permission but still cant execute sp
    ... that the stored procedure references in it's code. ... ADO connection sometimes opens a new connection to the server if the first ... > The stored procedure which I can't execute is below, ... >>> well as some users with no permissions. ...
    (microsoft.public.sqlserver.security)
  • Re: Exec permissions on Stored Procedure
    ... If I grant EXEC permissions on a Stored Procedure to a Domain User ... shouldn't they be able to Execute that stored procedure reguardless if they ...
    (microsoft.public.sqlserver.security)