Wsrtjtyk

Is it possible to get all database roles and server roles in the same query/script? if so then how can it be achieved?

The First query gives all the server roles and the second query gives following all the database roles, I can join the two queries with 'Union All' but the issue with the second query is it doesn't show any database Name which I need.

select @@SERVERNAME AS 'ServerName',

       logins.default_database_name AS DatabaseSchemaName,

       logins.name As LoginName,

       logins.type As Type,

    logins.type_desc As Type_Desc,

       sr.[name] COLLATE DATABASE_DEFAULT AS 'Roles&Permissions'                 

from  sys.server_principals logins 

inner join sys.server_role_members srm on logins.principal_id = srm. member_principal_id

Inner join sys.server_principals sr ON role_principal_id = sr.principal_id 

where logins.is_fixed_role <>1





SELECT @@SERVERNAME as 'Server Name',

       logins.default_schema_name AS SchemaName,

       logins.name AS Name,

    logins.type As Type,

    logins.type_desc AS Type_Desc,

       db.[name] COLLATE DATABASE_DEFAULT AS 'Roles&Permissions'                 

FROM  sys.database_principals logins 

inner join sys.database_role_members drm on logins.principal_id = drm.member_principal_id

Inner join sys.database_principals db ON role_principal_id = db.principal_id

The end goal is to get all the databases in the SQL server instance with the roles(e.g db_owner). This will be stored in a temp table from where I can export it to excel. So when the above queries didn't work, I wrote another one after doing some reading and online searching. In the following query, I am getting the database roles along with the respective database so that issue is resolved but I want server roles as well but don't know how to add those in this query.

DECLARE @DatabaseName SYSNAME,    

        @sql VARCHAR(1000)

DECLARE @ResultTable TABLE(DatabaseName VARCHAR(100), ServerName VARCHAR(100), Name VARCHAR(100), TypeDesc Varchar(100), DatabaseRole VARCHAR(100) )

DECLARE DatabaseCursor CURSOR 

     FOR SELECT Name FROM sys.databases

OPEN databaseCursor

FETCH NEXT FROM databaseCursor INTO @DatabaseName

WHILE (@@FETCH_STATUS = 0)

BEGIN

    SET @sql ='SELECT ''' + @DatabaseName + ''' AS [Database],

                      ''' +@@SERVERNAME +  '''AS [Server Name],

                      logins.name Name,

                      logins.type_desc TypeDesc, 

                      roles.name RoleName

               FROM '+@databaseName+'.sys.database_role_members rm

               INNER JOIN '+@databaseName +'.sys.database_principals roles ON rm.role_principal_id = roles.principal_id

               INNER JOIN '+@databaseName+'.sys.database_principals logins ON rm.member_principal_id = logins.principal_id'

    INSERT INTO @ResultTable EXEC(@sql)

    FETCH NEXT FROM databaseCursor INTO @databaseName

END

CLOSE databaseCursor 

DEALLOCATE databaseCursor 

SELECT * FROM @ResultTable;

If figuring out members with Server login permissions and also Database Roles can fulfil your requirement, then we may use sys.database_principals:

SELECT ROL.name AS Role_Name 

      ,MEM.name AS Member_Name 

      ,MEM.type_desc AS Member_Type 

      ,MEM.default_schema_name AS DefaultSchema 

      ,SP.name AS ServerLogin 

FROM sys.database_role_members AS DRM 

     INNER JOIN sys.database_principals AS ROL 

         ON DRM.role_principal_id = ROL.principal_id 

     INNER JOIN sys.database_principals AS MEM 

         ON DRM.member_principal_id = MEM.principal_id 

     INNER JOIN sys.server_principals AS SP 

         ON MEM.[sid] = SP.[sid] 

ORDER BY Role_Name 

        ,Member_Name;