Nirav's Diary

All about Microsoft Technology

Nirav's Diary

Main menu

Skip to primary content
Skip to secondary content
  • Home
  • Nirav Gajjar

Tag Archives: SQL Server Login Details

SQL Server Login Details

Posted on July 13, 2014 by Nirav Gajjar

SQL Server Login Details.

Server Role

SET NOCOUNT ON

;With Cte as
(
SELECT
 @@servername as ServerName,
 p.name AS LoginName,
 CASE WHEN p.is_disabled = 1 THEN 0 ELSE 1 END AS IsEnabled ,
 p.type_desc as LoginType,
 r.type_desc as RoleType,
 r.name as RoleName
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE r.type = 'R'
UNION ALL
select
 @@servername as ServerName,
 R.name AS LoginName,
 CASE WHEN r.is_disabled = 1 THEN 0 ELSE 1 END AS IsEnabled ,
 r.type_desc as LoginType,
 'SERVER_ROLE' as RoleType,
 'public' as RoleName
FROM sys.server_principals r
WHERE r.type IN ('S','U')
and r.name not like '##MS%'
)

--select * from cte

SELECT DISTINCT
r.ServerName,
r.LoginName,
r.IsEnabled,
r.LoginType,
r.RoleType,
STUFF(( SELECT distinct ', '+ a.RoleName
   FROM cte a
   WHERE r.ServerName = a.ServerName
    AND r.LoginName = a.LoginName
    AND r.IsEnabled = a.IsEnabled
    AND r.LoginType = a.LoginType
    AND r.RoleType = a.RoleType
  For XML PATH ('')),1,1,''
 ) as ServerRole
FROM cte r
order by ServerName, LoginType, RoleType, LoginName
Posted in Uncategorized | Tagged get SQL Server Login Details, SQL Server Login Details

Recent Posts

  • Query to check Integration failed jobs error message
  • SQL Server compatibility error in replication
  • Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF
  • Backup and Restore FileGroup
  • SQL Server Login Details

Archives

  • October 2014
  • August 2014
  • July 2014
  • June 2014
  • May 2014
  • January 2014
  • October 2013
  • July 2013
  • June 2013
  • May 2013
  • April 2013
  • March 2013
  • February 2013

Categories

  • AlwaysOn Availability Group
  • Dyanmic Connection
  • FailOver
  • Import Export
  • Jobs Detail
  • Monitroing Queries
  • Others
  • Replication
  • Script Task
  • Service Broker
  • SMO
  • SQL Server
  • SSIS
  • TSQL
  • Uncategorized
  • VBA
  • VBS
  • WMI
  • XML

Meta Description

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org
Proudly powered by WordPress