Prev: sunday is my week ending day
Next: Must declare the scalar variable "@myDB@"
From: Bodo on 1 Jun 2010 12:44
Hi,

Here's my DML statements that duplicates records from a
resultset qualfying to GESPRAECH_ID = @Quell_Gespraech_ID

Column [TB_GESPRAECHPARTNER].POS_ID has its property IDENTITY set to true.

SET IDENTITY_INSERT [TB_GESPRAECHPARTNER] ON

INSERT INTO [TB_GESPRAECHPARTNER]
([GESPRAECH_ID]
,POS_ID
,[KUNDENGRUPPE_ID]
,[KUNDE_INTERN_ID]
,[KUNDE_EXTERN_ID]
,[VERLAGSNUMMER] )
SELECT @Gespraech_id_Neu
, POS_ID
,[KUNDENGRUPPE_ID]
,[KUNDE_INTERN_ID]
,[KUNDE_EXTERN_ID]
,[VERLAGSNUMMER]
FROM [TB_GESPRAECHPARTNER]
Where GESPRAECH_ID = @Quell_Gespraech_ID
SET IDENTITY_INSERT [TB_GESPRAECHPARTNER] OFF


-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-- 5. dbo.TB_GESPRAECHPARTNER_TEILNEHMER duplizieren:
-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

INSERT INTO [TB_GESPRAECHPARTNER_TEILNEHMER]
([GESPRAECH_ID]
,[POS_ID]
,[TEILNEHMER_NAME]
,[TEILNEHMER_VORNAME]
,[BEMERKUNG]
)
SELECT @Gespraech_id_Neu
,[POS_ID]
,[TEILNEHMER_NAME]
,[TEILNEHMER_VORNAME]
,[BEMERKUNG]
FROM [dbo].[TB_GESPRAECHPARTNER_TEILNEHMER]
Where GESPRAECH_ID = @Quell_Gespraech_ID

The above works fine if you of own the object, or are a member of the
sysadmin fixed server role, or the db_owner and db_ddladmin fixed database
roles

But users executing the statements above don't have the permissions, so they
get a permission denied error.

Since I don't want to add my users to sysadm role I think about to eliminate
identity property of that column POS_ID and generate serial values in a
trigger statement, but not happy with that solution.

Anyone can help with an easier and more consistent approach?

Many thanks in advance!



--
Thanks in advance
Bodo
From: John Bell on 1 Jun 2010 13:28
On Tue, 1 Jun 2010 09:44:01 -0700, Bodo
<Bodo(a)discussions.microsoft.com> wrote:

>Hi,
>
>Here's my DML statements that duplicates records from a
>resultset qualfying to GESPRAECH_ID = @Quell_Gespraech_ID
>
>Column [TB_GESPRAECHPARTNER].POS_ID has its property IDENTITY set to true.
>
>SET IDENTITY_INSERT [TB_GESPRAECHPARTNER] ON
>
>INSERT INTO [TB_GESPRAECHPARTNER]
> ([GESPRAECH_ID]
> ,POS_ID
> ,[KUNDENGRUPPE_ID]
> ,[KUNDE_INTERN_ID]
> ,[KUNDE_EXTERN_ID]
> ,[VERLAGSNUMMER] )
> SELECT @Gespraech_id_Neu
> , POS_ID
> ,[KUNDENGRUPPE_ID]
> ,[KUNDE_INTERN_ID]
> ,[KUNDE_EXTERN_ID]
> ,[VERLAGSNUMMER]
> FROM [TB_GESPRAECHPARTNER]
> Where GESPRAECH_ID = @Quell_Gespraech_ID
> SET IDENTITY_INSERT [TB_GESPRAECHPARTNER] OFF
>
>
> -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> -- 5. dbo.TB_GESPRAECHPARTNER_TEILNEHMER duplizieren:
> -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
> INSERT INTO [TB_GESPRAECHPARTNER_TEILNEHMER]
> ([GESPRAECH_ID]
> ,[POS_ID]
> ,[TEILNEHMER_NAME]
> ,[TEILNEHMER_VORNAME]
> ,[BEMERKUNG]
> )
> SELECT @Gespraech_id_Neu
> ,[POS_ID]
> ,[TEILNEHMER_NAME]
> ,[TEILNEHMER_VORNAME]
> ,[BEMERKUNG]
> FROM [dbo].[TB_GESPRAECHPARTNER_TEILNEHMER]
> Where GESPRAECH_ID = @Quell_Gespraech_ID
>
>The above works fine if you of own the object, or are a member of the
>sysadmin fixed server role, or the db_owner and db_ddladmin fixed database
>roles
>
>But users executing the statements above don't have the permissions, so they
>get a permission denied error.
>
>Since I don't want to add my users to sysadm role I think about to eliminate
>identity property of that column POS_ID and generate serial values in a
>trigger statement, but not happy with that solution.
>
>Anyone can help with an easier and more consistent approach?
>
>Many thanks in advance!

From BOL the permissions required for setting IDENTITY_INSERT is

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.

Where does @Quell_Gespraech_ID come from? Assuming that you are
using unique values then why not use the SCOPE_IDENTITY function to
get the new value?

John


From: Erland Sommarskog on 1 Jun 2010 17:48
Bodo (Bodo(a)discussions.microsoft.com) writes:
> The above works fine if you of own the object, or are a member of the
> sysadmin fixed server role, or the db_owner and db_ddladmin fixed database
> roles
>
> But users executing the statements above don't have the permissions, so
> they get a permission denied error.
>

> Since I don't want to add my users to sysadm role I think about to
> eliminate identity property of that column POS_ID and generate serial
> values in a trigger statement, but not happy with that solution.

Not that I like IDENTITY_INSERT, and I think that if you need this
in applicaton code, you should consider not using IDENTITY at all.

Nevertheless, I have an article on my web site that describes what you
could try. The article does not discuss IDENTITY_INSERT per se, but
the ideas in the article are applicable to your case as well.


--
Erland Sommarskog, SQL Server MVP, esquel(a)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

From: Bodo on 2 Jun 2010 06:39
Thanks John & Erland,

I now was able to find a workaround

The code sample in my initial post is embedded in a stored procedure.

When I create the procedure WITH EXECUTE AS OWNER
the SET IDENTITY_INSERT ... works fine,
even when a user without sufficient permissions executes it.

Again many thanks.





 | 
Pages: 1
Prev: sunday is my week ending day
Next: Must declare the scalar variable "@myDB@"