tsql - SQL Server query to get users and related roles listed on one line -


i have output of sql server logins (sql 2005+) , server roles, every login need roles listed on 1 line, example:

hostname   username    desc           roles server     sa          sql_login      sysadmin server     john        sql_login      dbcreator,securityadmin server     domain\jack windows_login  diskadmin,setupadmin,dbcreator 

this should meet expectations:

select sp.name,        sp.type_desc,        (         select sp2.name + ',' [text()]          sys.server_role_members srm rolename              left join sys.server_principals sp2 on srm.role_principal_id = sp2.principal_id         srm.member_principal_id = sp.principal_id xml path ('')        )     sys.server_principals sp 

Comments

Popular posts from this blog

java - WARN : org.springframework.web.servlet.PageNotFound - No mapping found for HTTP request with URI [/board/] in DispatcherServlet with name 'appServlet' -

android - How to create dynamically Fragment pager adapter -

html - Outlook 2010 Anchor (url/address/link) -