Tuesday, May 1, 2012

Who are the sysadmins in this sql server?

http://www.sqlservercentral.com/blogs/marlon-ribunal-sql-code-coffee-etc/2012/04/23/who-are-the-sysadmins-in-this-sql-server/


Marlon Ribunal - SQL, Code, Coffee, etc.


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

No comments:

Post a Comment