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.
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;goDROP TABLE [dbo].[UserRoleTest];GOCREATE TABLE [dbo].[UserRoleTest]( [LoginId] [varchar](35) NULL, [UserName] [varchar](35) NULL, [DBRole] [varchar](35) NULL) ON [PRIMARY]GOINSERT 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;goIF OBJECT_ID('dbo.usp_test', 'p') IS NOT NULL DROP PROCedure dbo.usp_testGOCREATE PROCedure dbo.usp_testASSET 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 @tSET @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 ) ENDGO------------------------------------------------------------------------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 + @crlfFROM( 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)) stmtsORDER BY LoginId, CASE WHEN stmt like 'CREATE USER%' or stmt like 'ALTER USER%' THEN 1 ELSE 2 ENDFOR XML PATH('') |
|
|
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;--ErrorMsg 156, Level 15, State 1, Line 1Incorrect 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 + @crlfFROM( 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)) stmtsORDER BY LoginId, CASE WHEN stmt like 'CREATE USER%' or stmt like 'ALTER USER%' THEN 1 ELSE 2 ENDFOR XML PATH('')
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-01-30 : 23:10:27
|
That's the crlf you wanted |
|
|
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 |
|
|
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 |
|
|
|
|
|
|
|