Need help remove duplicate rows.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need help remove duplicate rows.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-01-19 : 11:25:12

Hi,

I am try to run the sp which after the restore is done but getting error with dup rows.
Please look at the rule below. Any help would greatly appreciated. SQL 2012

Thank you.
--------------------------------------------------------------------------------------

USE Tempdb;
IF OBJECT_ID('dbo.UserPermission1', 'u') IS NOT NULL
DROP TABLE dbo.UserPermission1
GO

CREATE TABLE dbo.UserPermission1
(
DBName VARCHAR(40) NULL
,LoginId VARCHAR(35) NULL
,DBRole VARCHAR(35) NULL
);
GO

INSERT INTO dbo.UserPermission1([DBName],[LoginId],[DBRole])
VALUES ('QAdb', '[Peter]', '[db_datareader]')

INSERT INTO dbo.UserPermission1([DBName],[LoginId],[DBRole])
VALUES ('QAdb', '[Mydomain\JSmith]', '[db_datareader]')
INSERT INTO dbo.UserPermission1([DBName],[LoginId],[DBRole])
VALUES ('QAdb', '[Mydomain\JSmith]', '[db_datawriter]')

INSERT INTO dbo.UserPermission1 ([DBName],[LoginId],[DBRole])
VALUES ('Dev', '[Lisa]', '[db_datareader]')
INSERT INTO dbo.UserPermission1([DBName],[LoginId],[DBRole])
VALUES ('Dev', '[Lisa]', '[db_datawriter]')
INSERT INTO dbo.UserPermission1 ([DBName],[LoginId],[DBRole])
VALUES ('Dev', '[Mydomain\JNguyen]', '[db_datareader]')
INSERT INTO dbo.UserPermission1 ([DBName],[LoginId],[DBRole])
VALUES ('Dev', '[Mydomain\JNguyen]', '[db_datawriter]')

INSERT INTO dbo.UserPermission1([DBName],[LoginId],[DBRole])
VALUES ('Testdb', '[Mydomain\PSmith]', '[db_datareader]')
INSERT INTO dbo.UserPermission1([DBName],[LoginId],[DBRole])
VALUES ('Tested', '[Mydomain\PSmith]', '[db_datawriter]');

SELECT *
FROM dbo.UserPermission1;
------------------------------------------------------------------
IF OBJECT_ID('dbo.usp_AdminApplyUserRight', 'p') IS NOT NULL
DROP PROCedure dbo.usp_AdminApplyUserRight
GO

CREATE PROCedure dbo.usp_AdminApplyUserRight
(
@DBName VARCHAR(40) = NULL
)
AS
SET NOCOUNT ON;
DECLARE @SQLStr VARCHAR(8000)
,@iCounterId INT
,@LoginId VARCHAR(35)
,@sLoginId VARCHAR(35)
,@DBRole VARCHAR(35)
,@crlf CHAR(1) = CHAR(13) + CHAR(10) -- carriage return, new line.

--PRINT '/******************************************************************************************'
--PRINT 'Servername: ' + @@SERVERNAME
--PRINT 'DatabaseName: ' + db_name()
--PRINT 'Generate date: ' + CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 126) + RIGHT(CONVERT(VARCHAR(35), GETDATE(), 109), 14)

--PRINT '*******************************************************************************************/'
--------------------------------------------------------

IF (@DBName IS NULL)
BEGIN
RAISERROR ('Please enter @DBName.', 16, 1)
RETURN (1) -- Failed.
END

--------------------------------------------------------------------------------------------------
DECLARE @db TABLE
(
iCounterId INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED
,LoginId VARCHAR(35) NULL
,DBRole VARCHAR(35) NULL
,DBName VARCHAR(40) NULL
);

INSERT @db (LoginId, DBRole, DBName)
SELECT LoginId, DBRole, DBName
FROM dbo.UserPermission1
WHERE (DBName = @DBName);

PRINT '-- @db'
--SELECT * FROM @db

PRINT 'USE ' + @DBName + ';'
SET @iCounterId = ( SELECT MIN(iCounterId)
FROM @db )

WHILE ( @iCounterId IS NOT NULL )
BEGIN

IF ( @iCounterId IS NULL )
BEGIN
PRINT 'Exit'
BREAK
END

----------------------------------------------------------
SELECT @LoginId = LoginId
,@DBRole = DBRole
FROM @db
WHERE (iCounterId = @iCounterId )

SET @SQLStr = + 'EXECute sp_revokedbaccess ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr

SET @SQLStr = 'EXECute sp_grantdbaccess ' + RTRIM(@LoginId) + ', ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr

SET @SQLStr = 'EXECute sp_addrolemember ' + RTRIM(@DBRole) + ', ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr

-------------------------------------------------------------------
--'USE ' + @DBName + ';' + @crlf
--SET @SQLStr = 'USE ' + @DBName + ';' + @crlf
----PRINT @SQLStr
--SET @SQLStr = @SQLStr + 'EXECute sp_revokedbaccess ' + RTRIM(@LoginId) + ';' + @crlf +
-- 'EXECute sp_grantdbaccess ' + RTRIM(@LoginId) + ', ' + RTRIM(@LoginId) + ';' + @crlf +
-- 'EXECute sp_addrolemember ' + RTRIM(@DBRole) + ', ' + RTRIM(@LoginId) + ';' + @crlf
--PRINT @SQLStr

----------------------------------------------------------

SET @iCounterId = ( SELECT MIN(iCounterId)
FROM @db
WHERE iCounterId > @iCounterId )
END
GO

---------------------------------------------------------------------------------------------------------

-- PRINT db_name()
EXECute dbo.usp_AdminApplyUserRight @DBName = 'Dev'

-- Rules:
-- Remove duplicate sp_revokedbaccess and sp_grantdbaccess when run the sp.

/*
-- Result want:
================
USE Dev;
EXECute sp_revokedbaccess [Lisa];
EXECute sp_grantdbaccess [Lisa], [Lisa];
EXECute sp_addrolemember [db_datareader], [Lisa];
EXECute sp_addrolemember [db_datawriter], [Lisa];

EXECute sp_revokedbaccess [Mydomain\JNguyen];
EXECute sp_grantdbaccess [Mydomain\JNguyen], [Mydomain\JNguyen];
EXECute sp_addrolemember [db_datareader], [Mydomain\JNguyen];
EXECute sp_addrolemember [db_datawriter], [Mydomain\JNguyen];

*/

/*
-- Should be removed.
USE Dev;
--EXECute sp_revokedbaccess [Lisa]; -- remove
--EXECute sp_grantdbaccess [Lisa], [Lisa]; -- remove
--EXECute sp_revokedbaccess [Mydomain\JNguyen]; -- remove
--EXECute sp_grantdbaccess [Mydomain\JNguyen], [Mydomain\JNguyen]; -- remove

*/

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-20 : 03:49:41

alter PROCedure dbo.usp_AdminApplyUserRight
(
@DBName VARCHAR(40) = NULL
)
AS
SET NOCOUNT ON;
DECLARE @SQLStr VARCHAR(8000)
,@iCounterId INT
,@LoginId VARCHAR(35)
,@sLoginId VARCHAR(35)
,@DBRole VARCHAR(35)
,@prevLogin VARCHAR(35)

IF (@DBName IS NULL)
BEGIN
RAISERROR ('Please enter @DBName.', 16, 1)
RETURN (1) -- Failed.
END

DECLARE @db TABLE
(
iCounterId INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED
,LoginId VARCHAR(35) NULL
,DBRole VARCHAR(35) NULL
,DBName VARCHAR(40) NULL
);

INSERT @db (LoginId, DBRole, DBName)
SELECT LoginId, DBRole, DBName
FROM dbo.UserPermission1
WHERE (DBName = @DBName);

PRINT '-- @db'
set @prevLogin = ''

PRINT 'USE ' + @DBName + ';'
SET @iCounterId = ( SELECT MIN(iCounterId)
FROM @db )

WHILE ( @iCounterId IS NOT NULL )
BEGIN

IF ( @iCounterId IS NULL )
BEGIN
PRINT 'Exit'
BREAK
END

----------------------------------------------------------
SELECT @LoginId = LoginId
,@DBRole = DBRole
FROM @db
WHERE (iCounterId = @iCounterId )

if @prevLogin <> @LoginId
begin
SET @SQLStr = + 'EXECute sp_revokedbaccess ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr

SET @SQLStr = 'EXECute sp_grantdbaccess ' + RTRIM(@LoginId) + ', ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr
end

SET @SQLStr = 'EXECute sp_addrolemember ' + RTRIM(@DBRole) + ', ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr

set @prevLogin = @LoginId
SET @iCounterId = ( SELECT MIN(iCounterId)
FROM @db
WHERE iCounterId > @iCounterId )
END
GO

Regards
Viggneshwar A

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-01-21 : 23:33:29

Viggneshwar,

I try different method but your is working. Thank you so much.

quote:
Originally posted by viggneshwar

alter PROCedure dbo.usp_AdminApplyUserRight
(
@DBName VARCHAR(40) = NULL
)
AS
SET NOCOUNT ON;
DECLARE @SQLStr VARCHAR(8000)
,@iCounterId INT
,@LoginId VARCHAR(35)
,@sLoginId VARCHAR(35)
,@DBRole VARCHAR(35)
,@prevLogin VARCHAR(35)

IF (@DBName IS NULL)
BEGIN
RAISERROR ('Please enter @DBName.', 16, 1)
RETURN (1) -- Failed.
END

DECLARE @db TABLE
(
iCounterId INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED
,LoginId VARCHAR(35) NULL
,DBRole VARCHAR(35) NULL
,DBName VARCHAR(40) NULL
);

INSERT @db (LoginId, DBRole, DBName)
SELECT LoginId, DBRole, DBName
FROM dbo.UserPermission1
WHERE (DBName = @DBName);

PRINT '-- @db'
set @prevLogin = ''

PRINT 'USE ' + @DBName + ';'
SET @iCounterId = ( SELECT MIN(iCounterId)
FROM @db )

WHILE ( @iCounterId IS NOT NULL )
BEGIN

IF ( @iCounterId IS NULL )
BEGIN
PRINT 'Exit'
BREAK
END

----------------------------------------------------------
SELECT @LoginId = LoginId
,@DBRole = DBRole
FROM @db
WHERE (iCounterId = @iCounterId )

if @prevLogin <> @LoginId
begin
SET @SQLStr = + 'EXECute sp_revokedbaccess ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr

SET @SQLStr = 'EXECute sp_grantdbaccess ' + RTRIM(@LoginId) + ', ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr
end

SET @SQLStr = 'EXECute sp_addrolemember ' + RTRIM(@DBRole) + ', ' + RTRIM(@LoginId) + ';'
PRINT @SQLStr

set @prevLogin = @LoginId
SET @iCounterId = ( SELECT MIN(iCounterId)
FROM @db
WHERE iCounterId > @iCounterId )
END
GO

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources