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)
 Help with dynamic sequel.

Author  Topic 

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-01-30 : 10:47:35

This is simple but how can I return only distinct login name. Just want to return one set of values.
Please see the desire output and the rule below. I am using SQL2012.

Thank you for your help in advance.


USE Tempdb;
go

DROP TABLE [dbo].[UserRoleTest];
GO
CREATE TABLE [dbo].[UserRoleTest]
(
[LoginId] [varchar](35) NULL,
[UserName] [varchar](35) NULL,
[DBRole] [varchar](35) NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.UserRoleTest([LoginId],[UserName],[DBRole])
VALUES ('[MyDomain\Peter]', 'MyDomain\Peter', '[db_datareader]' ) ;

INSERT INTO dbo.UserRoleTest([LoginId],[UserName],[DBRole])
VALUES ('[MyDomain\Peter]', 'MyDomain\Peter', '[db_datawriter]' ) ;

INSERT INTO dbo.UserRoleTest([LoginId],[UserName],[DBRole])
VALUES ('[Home\John]', 'Home\John', '[db_datareader]' ) ;

INSERT INTO dbo.UserRoleTest([LoginId],[UserName],[DBRole])
VALUES ('User1', 'User1', '[db_datareader]' ) ;
INSERT INTO dbo.UserRoleTest ([LoginId],[UserName],[DBRole])
VALUES ('User1', 'User1', '[db_datawriter]' ) ;

SELECT *
FROM [dbo].[UserRoleTest]
go

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


USE Tempdb;
go
IF OBJECT_ID('dbo.usp_test', 'p') IS NOT NULL
DROP PROCedure dbo.usp_test
GO

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

SET @DBName = 'Tempdb'
---------------------------------------------------------

--ALTER USER and ALTER ROLE.

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

INSERT @t (LoginId, UserName, DBRole)

SELECT LoginId, UserName, DBRole
FROM dbo.UserRoleTest
--WHERE (DBName = @DBName);

--SELECT * FROM @t

SET @iCounterId = ( SELECT MIN(iCounterId)
FROM @t );

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

-- Pick 2 variables.
SELECT @LoginId = LoginId
,@UserName = UserName
,@DBRole = DBRole
FROM @t
WHERE (iCounterId = @iCounterId )

--SQL Statement
--PRINT 'LoginId: ' + @LoginId
--PRINT 'UserName: ' + @UserName
--PRINT 'DBRole: ' + @DBRole

-- This section create users if users do not exist.
SET @SQLStr = 'USE [' + RTRIM(@DBName) + '];' + @crlf
SET @SQLStr = @SQLStr + 'IF NOT EXISTS ( SELECT 1 FROM sys.sysusers WHERE [name] = ''' + RTRIM(@UserName) + ''' )' + CHAR(13) +
' BEGIN' + @crlf + ' CREATE USER ' + @LoginId + ' FOR LOGIN ' + @LoginId + ' WITH DEFAULT_SCHEMA = [' + @UserName + '];' + @crlf +
' ALTER ROLE ' + RTRIM(@DBRole) + ' ADD MEMBER ' + RTRIM(@LoginId) + ';' + @crlf + ' END;' + CHAR(13) +
'ELSE ' + CHAR(13) + ' BEGIN' + @crlf + ' ALTER USER ' + RTRIM(@LoginId) + ' WITH LOGIN = ' + RTRIM(@LoginId) + ';' + CHAR(13) +
' ALTER ROLE ' + RTRIM(@DBRole) + ' ADD MEMBER ' + RTRIM(@LoginId) + ';' + @crlf + ' END;' + @crlf

PRINT @SQLStr

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

EXECute dbo.usp_test


/*

-- Result want:
-- Just return 1 set like below. Thank you so much.


USE [Tempdb];
IF NOT EXISTS ( SELECT 1 FROM sys.sysusers WHERE [name] = 'MyDomain\Peter' )

BEGIN
CREATE USER [MyDomain\Peter] FOR LOGIN [MyDomain\Peter] WITH DEFAULT_SCHEMA = [MyDomain\Peter];
ALTER ROLE [db_datareader] ADD MEMBER [MyDomain\Peter];
ALTER ROLE [db_datawriter] ADD MEMBER [MyDomain\Peter];
END;
ELSE
BEGIN
ALTER USER [MyDomain\Peter] WITH LOGIN = [MyDomain\Peter];
ALTER ROLE [db_datareader] ADD MEMBER [MyDomain\Peter];
ALTER ROLE [db_datawriter] ADD MEMBER [MyDomain\Peter];
END;


*/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-30 : 13:49:02
Since you're on 2012, here's an approach using LAG, along with XML PATH:


DECLARE @createuser nvarchar(200) = 'CREATE USER {login} FOR LOGIN {login} WITH DEFAULT SCHEMA = {username};'
DECLARE @alteruser nvarchar(200) = 'ALTER USER {login} WITH LOGIN = {login};'
DECLARE @alterrole nvarchar(200) = 'ALTER ROLE {dbrole} ADD MEMBER {login};'
DECLARE @crlf CHAR(1) = CHAR(13) + CHAR(10) -- carriage return, new line.

SELECT stmt + @crlf
FROM
(
SELECT LoginId, UserName, usr.isdefined,
CASE WHEN LAG(LoginId,1) OVER(ORDER BY LoginId) <> LoginId
OR LAG(LoginId,1) OVER(ORDER BY LoginId) IS NULL
THEN CASE WHEN usr.isdefined is null
THEN REPLACE(REPLACE(@createuser, '{login}', LoginId), '{username}', quotename(Username))
ELSE REPLACE(@alteruser, '{login}', LoginId)
END + @crlf
ELSE ''
END
+
REPLACE(REPLACE(@alterrole, '{dbrole}', DBRole), '{login}', LoginId)
AS stmt

FROM dbo.UserRoleTest urt

OUTER APPLY (
SELECT 1
FROM sys.sysusers users
WHERE users.name = urt.UserName
) usr(isdefined)
) stmts

ORDER BY LoginId,
CASE
WHEN stmt like 'CREATE USER%' or stmt like 'ALTER USER%'
THEN 1
ELSE 2
END
FOR XML PATH('')
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-01-30 : 16:45:12
When I execute your query, I got errors below. What is all #x0D???

Thanks.

CREATE USER [Home\John] FOR LOGIN [Home\John] WITH DEFAULT SCHEMA = [Home\John];#x0D;

ALTER ROLE [db_datareader] ADD MEMBER [Home\John];#x0D;
CREATE USER [MyDomain\Peter] FOR LOGIN [MyDomain\Peter] WITH DEFAULT SCHEMA = [MyDomain\Peter];#x0D;ALTER ROLE [db_datareader] ADD MEMBER [MyDomain\Peter];#x0D;ALTER ROLE [db_datawriter] ADD MEMBER [MyDomain\Peter];#x0D;CREATE USER User1 FOR LOGIN User1 WITH DEFAULT SCHEMA = [User1];#x0D;ALTER ROLE [db_datareader] ADD MEMBER User1;#x0D;ALTER ROLE [db_datawriter] ADD MEMBER User1;#x0D;


--Error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DEFAULT'.


quote:
Originally posted by gbritton

Since you're on 2012, here's an approach using LAG, along with XML PATH:


DECLARE @createuser nvarchar(200) = 'CREATE USER {login} FOR LOGIN {login} WITH DEFAULT SCHEMA = {username};'
DECLARE @alteruser nvarchar(200) = 'ALTER USER {login} WITH LOGIN = {login};'
DECLARE @alterrole nvarchar(200) = 'ALTER ROLE {dbrole} ADD MEMBER {login};'
DECLARE @crlf CHAR(1) = CHAR(13) + CHAR(10) -- carriage return, new line.

SELECT stmt + @crlf
FROM
(
SELECT LoginId, UserName, usr.isdefined,
CASE WHEN LAG(LoginId,1) OVER(ORDER BY LoginId) <> LoginId
OR LAG(LoginId,1) OVER(ORDER BY LoginId) IS NULL
THEN CASE WHEN usr.isdefined is null
THEN REPLACE(REPLACE(@createuser, '{login}', LoginId), '{username}', quotename(Username))
ELSE REPLACE(@alteruser, '{login}', LoginId)
END + @crlf
ELSE ''
END
+
REPLACE(REPLACE(@alterrole, '{dbrole}', DBRole), '{login}', LoginId)
AS stmt

FROM dbo.UserRoleTest urt

OUTER APPLY (
SELECT 1
FROM sys.sysusers users
WHERE users.name = urt.UserName
) usr(isdefined)
) stmts

ORDER BY LoginId,
CASE
WHEN stmt like 'CREATE USER%' or stmt like 'ALTER USER%'
THEN 1
ELSE 2
END
FOR XML PATH('')


Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-30 : 23:10:27
That's the crlf you wanted
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-31 : 07:00:03
Also, since we're using FOR XML, the result is run through the character escaping routines. So the CR/LF is first replaced with just a carriage return then escaped to #X0D -- its hex equivalent. The proper way to resolve this for your application is to use the XML function values() to extract the value as-is. Here's how you do that:


DECLARE @createuser nvarchar(200) = 'CREATE USER {login} FOR LOGIN {login} WITH DEFAULT SCHEMA = {username};'
DECLARE @alteruser nvarchar(200) = 'ALTER USER {login} WITH LOGIN = {login};'
DECLARE @alterrole nvarchar(200) = 'ALTER ROLE {dbrole} ADD MEMBER {login};'
DECLARE @crlf CHAR(1) = CHAR(13) + CHAR(10) -- carriage return, new line.
DECLARE @sql nvarchar(4000);

SET @sql =
(
SELECT stmt + @crlf
FROM
(
SELECT LoginId, UserName, usr.isdefined,
CASE WHEN LAG(LoginId,1) OVER(ORDER BY LoginId) <> LoginId
OR LAG(LoginId,1) OVER(ORDER BY LoginId) IS NULL
THEN CASE WHEN usr.isdefined is null
THEN REPLACE(REPLACE(@createuser, '{login}', LoginId), '{username}', quotename(Username))
ELSE REPLACE(@alteruser, '{login}', LoginId)
END + @crlf
ELSE ''
END
+
REPLACE(REPLACE(@alterrole, '{dbrole}', DBRole), '{login}', LoginId)
AS stmt

FROM dbo.UserRoleTest urt

OUTER APPLY (
SELECT 1
FROM sys.sysusers users
WHERE users.name = urt.UserName
) usr(isdefined)
) stmts

ORDER BY LoginId,
CASE
WHEN stmt like 'CREATE USER%' or stmt like 'ALTER USER%'
THEN 1
ELSE 2
END
FOR XML PATH(''), type
).value('.', 'nvarchar(4000)')
print @sql
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-01-31 : 15:33:10
[code]
,@crlf CHAR(1) = CHAR(13) + CHAR(10) -- carriage return, new line.
,@crlf CHAR(2) = CHAR(13) + CHAR(10) -- carriage return, new line.[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -