Help with TSQL please. - SQL Server Forums

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 TSQL please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-02-27 : 00:07:03

If a user belong to server role, I want to return unique server role. Please
see the result want below. I am using SQL 2012.

Thank you so much.

USE Tempdb;
go

DROP TABLE [dbo].[DBUser], [dbo].[ServerRole]
CREATE TABLE [dbo].[DBUser]
(
[loginname] [varchar](15) NULL,
[dbname] [varchar](15) NULL,
[dbrole] [varchar](15) NULL,
[typedesc] [varchar](15) NULL
)
GO

CREATE TABLE [dbo].[ServerRole]
(
[serverrole] [varchar](15) NULL,
[loginname] [varchar](15) NULL
) ON [PRIMARY]

GO

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\Peter', 'PC', 'db_ddladmin', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\Peter', 'PC', 'db_datareader', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\Peter', 'PC', 'db_datawriter', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\John', 'CC', 'db_datareader', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\John', 'CC', 'db_datawriter', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\John', 'Dev', 'db_datareader', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Robert', 'Dev', 'db_datareader', 'SQL_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\John', 'Dev', 'db_datawriter', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Robert', 'Dev', 'db_datawriter', 'SQL_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\John', 'BC', 'db_owner', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\John', 'BC', 'db_datareader', 'WINDOWS_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Rober', 'BC', 'db_datareader', 'SQL_USER' ) ;

INSERT INTO dbo.DBUser([loginname],[dbname],[dbrole],[typedesc])
VALUES ('Home\John', 'BC', 'db_datawriter', 'WINDOWS_USER' ) ;

go

INSERT INTO dbo.ServerRole([serverrole],[loginname])
VALUES ('sysadmin', 'Home\John' ) ;

INSERT INTO dbo.ServerRole([serverrole],[loginname])
VALUES ('sysadmin', 'Robert' ) ;
INSERT INTO dbo.ServerRole([serverrole],[loginname])
VALUES ('securityadmin', 'Robert' ) ;

INSERT INTO dbo.ServerRole([serverrole],[loginname])
VALUES ('serveradmin', 'Home\John' ) ;

INSERT INTO dbo.ServerRole([serverrole],[loginname])
VALUES ('setupadmin', 'Home\John' ) ;
go

SELECT *
FROM DBUser;
GO

SELECT *
FROM ServerRole;
GO

loginname dbname dbrole typedesc
--------------- --------------- --------------- ---------------
Home\Peter PC db_ddladmin WINDOWS_USER
Home\Peter PC db_datareader WINDOWS_USER
Home\Peter PC db_datawriter WINDOWS_USER
Home\John CC db_datareader WINDOWS_USER
Home\John CC db_datawriter WINDOWS_USER
Home\John Dev db_datareader WINDOWS_USER
Robert Dev db_datareader SQL_USER
Home\John Dev db_datawriter WINDOWS_USER
Robert Dev db_datawriter SQL_USER
Home\John BC db_owner WINDOWS_USER
Home\John BC db_datareader WINDOWS_USER
Rober BC db_datareader SQL_USER
Home\John BC db_datawriter WINDOWS_USER

serverrole loginname
--------------- ---------------
sysadmin Home\John
sysadmin Robert
securityadmin Robert
serveradmin Home\John
setupadmin Home\John

SELECT a.loginname
,a.dbname
,a.dbrole
,b.serverrole
FROM DBUser AS a
JOIN ServerRole AS b
ON a.loginname = b.loginname;
GO

-- Result want:
loginname dbname dbrole serverrole
--------------- --------------- --------------- ---------------
Home\John CC db_datareader sysadmin
Home\John CC db_datawriter serveradmin
Home\John Dev db_datareader setupadmin
Home\John Dev db_datawriter
Home\John BC db_owner
Home\John BC db_datareader
Home\John BC db_datawriter
Robert Dev db_datareader sysadmin
Robert Dev db_datawriter securityadmin
Robert Dev db_datareader
Robert Dev db_datawriter
Home\John CC db_datareader
Home\John CC db_datawriter
Home\John Dev db_datareader
Home\John Dev db_datawriter
Home\John BC db_owner
Home\John BC db_datareader
Home\John BC db_datawriter
Home\John CC db_datareader
Home\John CC db_datawriter
Home\John Dev db_datareader
Home\John Dev db_datawriter
Home\John BC db_owner
Home\John BC db_datareader
Home\John BC db_datawriter

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2015-03-06 : 12:38:35

Could you explain logic to get the result ?

Why setupadmin appears only once in the result?

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-03-06 : 13:07:13

Because at a server level. Example John Doe is in sysadmin role and I don't want him repeat in every row.

quote:
Originally posted by namman

Could you explain logic to get the result ?

Why setupadmin appears only once in the result?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2015-03-06 : 14:46:18

Hi NguyenL71,

I guess you want the serverrolve displays only one time for each loginname. That's clear. The unclear is about mapping serverrole with dbname and dbrole.

Your expect result :

Home\John CC db_datareader sysadmin
Home\John CC db_datawriter serveradmin
Home\John Dev db_datareader setupadmin
Home\John Dev db_datawriter

For Home\John:
sysadmin must be with CC and db_datareader ?
serveradmin must be with CC and db_datawriter ?
setupadmin must be with Dev and setupadmin ?

Or just random ?

If not ramdom, what is the logic to map serverrole with dbname and dbrole ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources