|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
isting_of_server_rôles_for_a_loginI am looking for a script that can give me the list of server rôle attach to a login and the relative SQL user. Like this : LOGIN SQL_USER SERVER_ROLE ----------------- ---------------- ---------------------- sa dbo sysadmin a_login an_user bulkadmin .... and so on ! Thanks -- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com *********************** SELECT L.NAME AS [LOGIN],U.NAME AS [SQL_USER],'SYSADMIN' AS [SERVER_ROLE]
FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID WHERE IS_SRVROLEMEMBER('SYSADMIN',L.NAME) = 1 UNION ALL SELECT L.NAME,U.NAME,'DBCREATOR' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID WHERE IS_SRVROLEMEMBER('DBCREATOR',L.NAME) = 1 UNION ALL SELECT L.NAME,U.NAME,'BULKADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID WHERE IS_SRVROLEMEMBER('BULKADMIN',L.NAME) = 1 UNION ALL SELECT L.NAME,U.NAME,'PROCESSADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID WHERE IS_SRVROLEMEMBER('PROCESSADMIN',L.NAME) = 1 UNION ALL SELECT L.NAME,U.NAME,'SERVERADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID WHERE IS_SRVROLEMEMBER('SERVERADMIN',L.NAME) = 1 UNION ALL SELECT L.NAME,U.NAME,'SETUPADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID WHERE IS_SRVROLEMEMBER('SETUPADMIN',L.NAME) = 1 UNION ALL SELECT L.NAME,U.NAME,'SECURITYADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID WHERE IS_SRVROLEMEMBER('SECURITYADMIN',L.NAME) = 1 Show quote "Fred BROUARD" <broua***@club-internet.fr> wrote in message news:%23rNgJ5Z0GHA.4176@TK2MSFTNGP06.phx.gbl... > Hi, > > I am looking for a script that can give me the list of server rôle attach > to a login and the relative SQL user. > > Like this : > > LOGIN SQL_USER SERVER_ROLE > ----------------- ---------------- ---------------------- > sa dbo sysadmin > a_login an_user bulkadmin > ... > > and so on ! > > Thanks > > -- > Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL > Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com > Audit, conseil, expertise, formation, modélisation, tuning, optimisation > ********************* http://www.datasapiens.com *********************** This works for a single database. If you want to have it work for all
databases, create a temp table to hold your results and cursor through all the databases in SYSDATABASES. Also, build a database name into your result set. Otherwise the results will be meaningless since you will not know which DB the user is from. Chuck Hawkins Show quote "Dr. Network" <charles.hawk***@jenzabar.net> wrote in message news:%23zIP6La0GHA.4264@TK2MSFTNGP05.phx.gbl... > SELECT L.NAME AS [LOGIN],U.NAME AS [SQL_USER],'SYSADMIN' AS [SERVER_ROLE] > FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SYSADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'DBCREATOR' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS > U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('DBCREATOR',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'BULKADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS > U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('BULKADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'PROCESSADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN > SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('PROCESSADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'SERVERADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN > SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SERVERADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'SETUPADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN > SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SETUPADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'SECURITYADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN > SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SECURITYADMIN',L.NAME) = 1 > > > "Fred BROUARD" <broua***@club-internet.fr> wrote in message > news:%23rNgJ5Z0GHA.4176@TK2MSFTNGP06.phx.gbl... >> Hi, >> >> I am looking for a script that can give me the list of server rôle attach >> to a login and the relative SQL user. >> >> Like this : >> >> LOGIN SQL_USER SERVER_ROLE >> ----------------- ---------------- ---------------------- >> sa dbo sysadmin >> a_login an_user bulkadmin >> ... >> >> and so on ! >> >> Thanks >> >> -- >> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL >> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com >> Audit, conseil, expertise, formation, modélisation, tuning, optimisation >> ********************* http://www.datasapiens.com *********************** > > Dr. Network a écrit :
> This works for a single database. If you want to have it work for all Thanks very much !> databases, create a temp table to hold your results and cursor through all > the databases in SYSDATABASES. Also, build a database name into your result > set. Otherwise the results will be meaningless since you will not know which > DB the user is from. > > Chuck Hawkins A + Show quote > > "Dr. Network" <charles.hawk***@jenzabar.net> wrote in message > news:%23zIP6La0GHA.4264@TK2MSFTNGP05.phx.gbl... >> SELECT L.NAME AS [LOGIN],U.NAME AS [SQL_USER],'SYSADMIN' AS [SERVER_ROLE] >> FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID >> WHERE IS_SRVROLEMEMBER('SYSADMIN',L.NAME) = 1 >> UNION ALL >> SELECT L.NAME,U.NAME,'DBCREATOR' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS >> U ON L.SID = U.SID >> WHERE IS_SRVROLEMEMBER('DBCREATOR',L.NAME) = 1 >> UNION ALL >> SELECT L.NAME,U.NAME,'BULKADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS >> U ON L.SID = U.SID >> WHERE IS_SRVROLEMEMBER('BULKADMIN',L.NAME) = 1 >> UNION ALL >> SELECT L.NAME,U.NAME,'PROCESSADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN >> SYSUSERS U ON L.SID = U.SID >> WHERE IS_SRVROLEMEMBER('PROCESSADMIN',L.NAME) = 1 >> UNION ALL >> SELECT L.NAME,U.NAME,'SERVERADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN >> SYSUSERS U ON L.SID = U.SID >> WHERE IS_SRVROLEMEMBER('SERVERADMIN',L.NAME) = 1 >> UNION ALL >> SELECT L.NAME,U.NAME,'SETUPADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN >> SYSUSERS U ON L.SID = U.SID >> WHERE IS_SRVROLEMEMBER('SETUPADMIN',L.NAME) = 1 >> UNION ALL >> SELECT L.NAME,U.NAME,'SECURITYADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN >> SYSUSERS U ON L.SID = U.SID >> WHERE IS_SRVROLEMEMBER('SECURITYADMIN',L.NAME) = 1 >> >> >> "Fred BROUARD" <broua***@club-internet.fr> wrote in message >> news:%23rNgJ5Z0GHA.4176@TK2MSFTNGP06.phx.gbl... >>> Hi, >>> >>> I am looking for a script that can give me the list of server rôle attach >>> to a login and the relative SQL user. >>> >>> Like this : >>> >>> LOGIN SQL_USER SERVER_ROLE >>> ----------------- ---------------- ---------------------- >>> sa dbo sysadmin >>> a_login an_user bulkadmin >>> ... >>> >>> and so on ! >>> >>> Thanks >>> >>> -- >>> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL >>> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com >>> Audit, conseil, expertise, formation, modélisation, tuning, optimisation >>> ********************* http://www.datasapiens.com *********************** >> > > -- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com *********************** Just some corrections due to collation :
SELECT L.name AS LOGIN, U.name AS SQL_USER, 'sysadmin' AS SERVER_ROLE FROM master.dbo.syslogins L INNER JOIN sysusers U ON L.sid = U.sid WHERE IS_SRVROLEMEMBER('sysadmin',L.name) = 1 UNION ALL SELECT L.name,U.name,'dbcreator' FROM master.dbo.syslogins L INNER JOIN sysusers U ON L.sid = U.sid WHERE IS_SRVROLEMEMBER('dbcreator',L.name) = 1 UNION ALL SELECT L.name,U.name,'bulkadmin' FROM master.dbo.syslogins L INNER JOIN sysusers U ON L.sid = U.sid WHERE IS_SRVROLEMEMBER('bulkadmin',L.name) = 1 UNION ALL SELECT L.name,U.name,'processadmin' FROM master.dbo.syslogins L INNER JOIN sysusers U ON L.sid = U.sid WHERE IS_SRVROLEMEMBER('processadmin',L.name) = 1 UNION ALL SELECT L.name,U.name,'serveradmin' FROM master.dbo.syslogins L INNER JOIN sysusers U ON L.sid = U.sid WHERE IS_SRVROLEMEMBER('serveradmin',L.name) = 1 UNION ALL SELECT L.name,U.name,'setupadmin' FROM master.dbo.syslogins L INNER JOIN sysusers U ON L.sid = U.sid WHERE IS_SRVROLEMEMBER('setupadmin',L.name) = 1 UNION ALL SELECT L.name,U.name,'securityadmin' FROM master.dbo.syslogins L INNER JOIN sysusers U ON L.sid = U.sid WHERE IS_SRVROLEMEMBER('securityadmin',L.name) = 1 A + Dr. Network a écrit : Show quote > SELECT L.NAME AS [LOGIN],U.NAME AS [SQL_USER],'SYSADMIN' AS [SERVER_ROLE] > FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SYSADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'DBCREATOR' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U > ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('DBCREATOR',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'BULKADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS U > ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('BULKADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'PROCESSADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN > SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('PROCESSADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'SERVERADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS > U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SERVERADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'SETUPADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN SYSUSERS > U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SETUPADMIN',L.NAME) = 1 > UNION ALL > SELECT L.NAME,U.NAME,'SECURITYADMIN' FROM MASTER.DBO.SYSLOGINS L JOIN > SYSUSERS U ON L.SID = U.SID > WHERE IS_SRVROLEMEMBER('SECURITYADMIN',L.NAME) = 1 > > > "Fred BROUARD" <broua***@club-internet.fr> wrote in message > news:%23rNgJ5Z0GHA.4176@TK2MSFTNGP06.phx.gbl... >> Hi, >> >> I am looking for a script that can give me the list of server rôle attach >> to a login and the relative SQL user. >> >> Like this : >> >> LOGIN SQL_USER SERVER_ROLE >> ----------------- ---------------- ---------------------- >> sa dbo sysadmin >> a_login an_user bulkadmin >> ... >> >> and so on ! >> >> Thanks >> >> -- >> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL >> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com >> Audit, conseil, expertise, formation, modélisation, tuning, optimisation >> ********************* http://www.datasapiens.com *********************** > > -- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************  |
|||||||||||||||||||||||
Other interesting topics