Stored procedure terminated

Question

Stored procedure terminated

Virtual Reality

Ten Centuries

Points: 1182
More actions
February 16, 2016 at 10:24 am

#399730

Hello,
I am trying to run one stored procedure and it failed to run. And I got this error message when I was trying to execute. How could I solve this issue?
I have this error message when I was trying to execute.
Msg 2627, Level 14, State 1, Procedure IZENDA_Security_Update, Line 8
Violation of PRIMARY KEY constraint 'PK__#A835DE2__5B8F1485517ECD00'. Cannot insert duplicate key in object 'dbo.@IZObjects'. The duplicate key value is (IZ_AD_ATTRIBUTES_SEC_V).
The statement has been terminated.
I am also attaching the code here:
USE [TmsEPrd]
GO
/****** Object: StoredProcedure [dbo].[IZENDA_Security_Update] Script Date: 2/16/2016 10:21:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[IZENDA_Security_Update] AS
BEGIN
DECLARE @winlogins TABLE (acct_name sysname, acct_type varchar(10), act_priv varchar(10), login_name sysname, perm_path sysname);
DECLARE @IZObjects TABLE (ObjectName varchar(60) not null primary key);
DECLARE @RCTE_Members TABLE (MemberName sysname, ObjectName sysname, MemberType sysname);
DECLARE @IzSec TABLE (DBAppID int, UserID varchar(513), ObjectName varchar(513));
INSERT INTO @IZObjects
SELECT obj.name
FROM sys.database_principals roleprinc--Roles
JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions
JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE roleprinc.name = 'IZENDA_CATALOG_ROLE';
WITH cte_role_recursive (MemberName, ObjectName, RoleName, MemberType) AS
(
SELECT memberprinc.name, obj.name, roleprinc.name, memberprinc.type_desc
FROM sys.database_role_members members --Role/member associations
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Roles
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] --Role members (database users)
JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions
JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
UNION ALL
SELECT memberprinc.name, crr.ObjectName, roleprinc.name, memberprinc.type_desc
FROM sys.database_role_members members --Role/member associations
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Roles
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] --Role members (database users)
JOIN cte_role_recursive crr ON crr.MemberName = roleprinc.name
WHERE crr.MemberType = 'DATABASE_ROLE'
)
INSERT INTO @RCTE_Members (MemberName, ObjectName, MemberType)
SELECT DISTINCT MemberName, ObjectName, MemberType
FROM cte_role_recursive
WHERE MemberType <> 'DATABASE_ROLE';-- This leaves SQL_USER and WINDOWS_GROUP
IF (SELECT 1 FROM CONFIGURATION_TABL WHERE MOD_CDE = 'TL' AND FUNCT_CDE = 'GLOBAL_VARIABLE' AND CHAR_CDE = 'EX_AUTH_MODE' AND CONFIG_VALUE IN ('I', 'M')) = 1
BEGIN
DECLARE @group sysname;
DECLARE recscan CURSOR FORSELECT name FROM sys.database_principals WHERE TYPE = 'G' AND NAME NOT LIKE 'NT%'
UNION
SELECT DISTINCT MemberName FROM @RCTE_Members WHERE MemberType = 'WINDOWS_GROUP';
OPEN recscan;
FETCH NEXT FROM recscan INTO @group;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @winlogins EXEC xp_logininfo @group, 'members'
FETCH NEXT FROM recscan INTO @group
END
CLOSE recscan;
DEALLOCATE recscan;
END;
INSERT INTO @IzSec (DBAppID, UserID, ObjectName)
SELECT -1, princ.name [UserID], obj.name [ObjectName]
--List all access provisioned to a sql user or windows user/group directly
FROM sys.database_principals princ --database user
LEFT JOIN sys.server_principals ulogin on princ.[sid] = ulogin.[sid] --Login accounts
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] --Permissions
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
WHERE princ.[type] in ('S','U','G')
AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT -1, memberprinc.name, obj.name
FROM sys.database_role_members members --Role/member associations
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Roles
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] --Role members (database users)
AND memberprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
LEFT JOIN sys.server_principals ulogin on memberprinc.[sid] = ulogin.[sid] --Login accounts
JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions
JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
WHERE memberprinc.name NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest')
UNION
SELECT -1, ugroup.acct_name, obj.name
--List all access provisioned to a AD user/group through a database or application role
FROM @winlogins ugroup -- AD Groups that login is in
JOIN sys.database_principals roleprinc ON roleprinc.name = ugroup.perm_path --Roles
JOIN sys.database_role_members members ON members.member_principal_id = roleprinc.[principal_id] --Role/member associations
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.role_principal_id --Role members (database users)
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = memberprinc.[principal_id] --Permissions
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
WHERE memberprinc.name NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest')
UNION
SELECT DISTINCT -1, ugroup.acct_name, rcte.ObjectName
FROM @RCTE_Members rcte
JOIN @winlogins ugroup ON rcte.MemberName = ugroup.perm_path -- AD Groups that login is in
WHERE rcte.MemberType = 'WINDOWS_GROUP'
UNION
SELECT DISTINCT -1, rcte.MemberName, rcte.ObjectName
FROM @RCTE_Members rcte
WHERE rcte.MemberType = 'SQL_USER'
UNION
SELECT DISTINCT ds.IZ_RPT_DATABASE_APPID, p.UserID, ds.DB_OBJECT_NAME
FROM IZ_RPT_DATASOURCE_PERMISSIONS p
JOIN IZ_RPT_DATASOURCE ds ON p.IZ_RPT_DATASOURCE_APPID = ds.APPID
JOIN IZ_RPT_DATABASE db ON ds.IZ_RPT_DATABASE_APPID = db.APPID
WHERE db.DATABASE_CODE <> 'JenzabarEX';
DELETE FROM IzendaSecurity
FROM IzendaSecurity
LEFT OUTER JOIN @IzSec iz ON IzendaSecurity.UserID = iz.UserID AND IzendaSecurity.ObjectName = iz.ObjectName AND IzendaSecurity.IZ_RPT_DATABASE_APPID = iz.DBAppID
WHERE iz.UserID IS NULL;
INSERT INTO IzendaSecurity (UserID, ObjectName, IZ_RPT_DATABASE_APPID)
SELECT iz.UserID, iz.ObjectName, iz.DBAppID
FROM @IzSec iz
LEFT OUTER JOIN IzendaSecurity izs ON izs.ObjectName = iz.ObjectName AND izs.UserID = iz.UserID AND izs.IZ_RPT_DATABASE_APPID = iz.DBAppID
WHERE izs.UserID IS NULL;
END;
I need to solve this issue asap.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

ripas (2/16/2016)
Hello,
I am trying to run one stored procedure and it failed to run. And I got this error message when I was trying to execute. How could I solve this issue?
I have this error message when I was trying to execute.
Msg 2627, Level 14, State 1, Procedure IZENDA_Security_Update, Line 8
Violation of PRIMARY KEY constraint 'PK__#A835DE2__5B8F1485517ECD00'. Cannot insert duplicate key in object 'dbo.@IZObjects'. The duplicate key value is (IZ_AD_ATTRIBUTES_SEC_V).
The statement has been terminated.
I am also attaching the code here:
USE [TmsEPrd]
GO
/****** Object: StoredProcedure [dbo].[IZENDA_Security_Update] Script Date: 2/16/2016 10:21:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[IZENDA_Security_Update] AS
BEGIN
DECLARE @winlogins TABLE (acct_name sysname, acct_type varchar(10), act_priv varchar(10), login_name sysname, perm_path sysname);
DECLARE @IZObjects TABLE (ObjectName varchar(60) not null primary key);
DECLARE @RCTE_Members TABLE (MemberName sysname, ObjectName sysname, MemberType sysname);
DECLARE @IzSec TABLE (DBAppID int, UserID varchar(513), ObjectName varchar(513));
INSERT INTO @IZObjects
SELECT obj.name
FROM sys.database_principals roleprinc--Roles
JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions
JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE roleprinc.name = 'IZENDA_CATALOG_ROLE';
WITH cte_role_recursive (MemberName, ObjectName, RoleName, MemberType) AS
(
SELECT memberprinc.name, obj.name, roleprinc.name, memberprinc.type_desc
FROM sys.database_role_members members --Role/member associations
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Roles
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] --Role members (database users)
JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions
JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
UNION ALL
SELECT memberprinc.name, crr.ObjectName, roleprinc.name, memberprinc.type_desc
FROM sys.database_role_members members --Role/member associations
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Roles
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] --Role members (database users)
JOIN cte_role_recursive crr ON crr.MemberName = roleprinc.name
WHERE crr.MemberType = 'DATABASE_ROLE'
)
INSERT INTO @RCTE_Members (MemberName, ObjectName, MemberType)
SELECT DISTINCT MemberName, ObjectName, MemberType
FROM cte_role_recursive
WHERE MemberType <> 'DATABASE_ROLE';-- This leaves SQL_USER and WINDOWS_GROUP
IF (SELECT 1 FROM CONFIGURATION_TABL WHERE MOD_CDE = 'TL' AND FUNCT_CDE = 'GLOBAL_VARIABLE' AND CHAR_CDE = 'EX_AUTH_MODE' AND CONFIG_VALUE IN ('I', 'M')) = 1
BEGIN
DECLARE @group sysname;
DECLARE recscan CURSOR FORSELECT name FROM sys.database_principals WHERE TYPE = 'G' AND NAME NOT LIKE 'NT%'
UNION
SELECT DISTINCT MemberName FROM @RCTE_Members WHERE MemberType = 'WINDOWS_GROUP';
OPEN recscan;
FETCH NEXT FROM recscan INTO @group;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @winlogins EXEC xp_logininfo @group, 'members'
FETCH NEXT FROM recscan INTO @group
END
CLOSE recscan;
DEALLOCATE recscan;
END;
INSERT INTO @IzSec (DBAppID, UserID, ObjectName)
SELECT -1, princ.name [UserID], obj.name [ObjectName]
--List all access provisioned to a sql user or windows user/group directly
FROM sys.database_principals princ --database user
LEFT JOIN sys.server_principals ulogin on princ.[sid] = ulogin.[sid] --Login accounts
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] --Permissions
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
WHERE princ.[type] in ('S','U','G')
AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT -1, memberprinc.name, obj.name
FROM sys.database_role_members members --Role/member associations
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Roles
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] --Role members (database users)
AND memberprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
LEFT JOIN sys.server_principals ulogin on memberprinc.[sid] = ulogin.[sid] --Login accounts
JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions
JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
WHERE memberprinc.name NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest')
UNION
SELECT -1, ugroup.acct_name, obj.name
--List all access provisioned to a AD user/group through a database or application role
FROM @winlogins ugroup -- AD Groups that login is in
JOIN sys.database_principals roleprinc ON roleprinc.name = ugroup.perm_path --Roles
JOIN sys.database_role_members members ON members.member_principal_id = roleprinc.[principal_id] --Role/member associations
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.role_principal_id --Role members (database users)
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = memberprinc.[principal_id] --Permissions
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
JOIN @IZObjects izobj ON obj.name = izobj.ObjectName
WHERE memberprinc.name NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest')
UNION
SELECT DISTINCT -1, ugroup.acct_name, rcte.ObjectName
FROM @RCTE_Members rcte
JOIN @winlogins ugroup ON rcte.MemberName = ugroup.perm_path -- AD Groups that login is in
WHERE rcte.MemberType = 'WINDOWS_GROUP'
UNION
SELECT DISTINCT -1, rcte.MemberName, rcte.ObjectName
FROM @RCTE_Members rcte
WHERE rcte.MemberType = 'SQL_USER'
UNION
SELECT DISTINCT ds.IZ_RPT_DATABASE_APPID, p.UserID, ds.DB_OBJECT_NAME
FROM IZ_RPT_DATASOURCE_PERMISSIONS p
JOIN IZ_RPT_DATASOURCE ds ON p.IZ_RPT_DATASOURCE_APPID = ds.APPID
JOIN IZ_RPT_DATABASE db ON ds.IZ_RPT_DATABASE_APPID = db.APPID
WHERE db.DATABASE_CODE <> 'JenzabarEX';
DELETE FROM IzendaSecurity
FROM IzendaSecurity
LEFT OUTER JOIN @IzSec iz ON IzendaSecurity.UserID = iz.UserID AND IzendaSecurity.ObjectName = iz.ObjectName AND IzendaSecurity.IZ_RPT_DATABASE_APPID = iz.DBAppID
WHERE iz.UserID IS NULL;
INSERT INTO IzendaSecurity (UserID, ObjectName, IZ_RPT_DATABASE_APPID)
SELECT iz.UserID, iz.ObjectName, iz.DBAppID
FROM @IzSec iz
LEFT OUTER JOIN IzendaSecurity izs ON izs.ObjectName = iz.ObjectName AND izs.UserID = iz.UserID AND izs.IZ_RPT_DATABASE_APPID = iz.DBAppID
WHERE izs.UserID IS NULL;
END;
I need to solve this issue asap.
Thanks

Use DISTINCT

INSERT INTO @IZObjects

SELECT DISTINCT obj.name

FROM sys.database_principals roleprinc--Roles

JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions

JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]

WHERE roleprinc.name = 'IZENDA_CATALOG_ROLE';

Or change the query entirely.

INSERT INTO @IZObjects

SELECT obj.name

FROM sys.objects obj

WHERE EXISTS (SELECT 1

FROM sys.database_principals roleprinc--Roles

JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] --Permissions

WHERE perm.[major_id] = obj.[object_id]

AND roleprinc.name = 'IZENDA_CATALOG_ROLE');

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Virtual Reality Ten Centuries Points: 1182 More actions · Answer 2

Thanks for your reply. I am trying to install izenda. And it creates this procedure automatically during installation. But unfortunately this procedure failed and I couldn't give permissions to anyone. So I am trying to figure it out that why it is failing.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Same object has 2 or more permissions granted to it under the specified role.

Which is pretty typical.

For example, tables usually have permissions to INSERT, DELETE and SELECT for the same user.

Software which cannot handle it is not really worth installing.

If they failed on such an obvious case there is no hope they have not screwed up in other, not so simple, cases.

_____________
Code for TallyGenerator