How to add servername into a query.

Question

Post reply

How to add servername into a query.

Chitown

Hall of Fame

Points: 3239
More actions
March 14, 2016 at 10:48 am

#325710

I am wondering if I can get some help on this? I have this query which gives me all the information of the logins with sysadmin rights but I need to find out the server name as well. I am not able to find a way to link any tables with sys.sysservers or sys.servers.
USE master
GO
SELECT p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
-- Logins that are sysadmins
AND s.sysadmin = 1
Go

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

SELECT name FROM sys.servers WHERE server_id = 0

SELECT SERVERPROPERTY('ServerName')

John

Chitown Hall of Fame Points: 3239 More actions · Answer 2

John Mitchell-245523 (3/14/2016)
SELECT name FROM sys.servers WHERE server_id = 0
SELECT SERVERPROPERTY('ServerName')
John

Thanks for the response.

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

Just add it to your query?

SELECT @@SERVERNAME,

p.name AS [loginname] ,

p.type ,

p.type_desc ,

p.is_disabled,

CONVERT(VARCHAR(10),p.create_date ,101) AS [created],

CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]

FROM sys.server_principals p

JOIN sys.syslogins s ON p.sid = s.sid

WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')

-- Logins that are not process logins

AND p.name NOT LIKE '##%'

-- Logins that are sysadmins

AND s.sysadmin = 1;

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