Is it possible to get all the database roles and server roles in the same query / script? If so, how can it be achieved?
Initially, I wrote two separate queries, one for server roles and one for database roles. I joined the two queries with & # 39; Union All & # 39 ;, but the problem is that it does not show any database name that I need.
So I approached the problem from a new angle, since the ultimate goal is to get all the databases in the instance of the SQL server with the roles (for example, db_owner). This will be stored in a temporary table from where I can export it to Excel. So I wrote a script after reading and searching online. In the following query, I get the roles of the database along with the corresponding database so that the problem is solved, but I also want server roles and I do not know how to get those that use this query. If someone can help me modify the script so that it provides the server and database roles along with the server and the name of the database.
DECLARE THE NAME OF THE SYSTEM SYSTEM, @sql VARCHAR (1000) DECLARE TABLE @ResultTable (DatabaseName VARCHAR (100), ServerName VARCHAR (100), Name VARCHAR (100), TypeDesc Varchar (100), DatabaseRole VARCHAR (100)) DECLARE CURSOR DatabaseCursor TO SELECT Name of sys.databases Open databaseCursor FETCH NEXT FROM databaseCursor INTO @DatabaseName WHILE (@@ FETCH_STATUS = 0) START SET @sql = & # 39; SELECT & # 39; & # 39; & # 39; + @DatabaseName + & # 39; & # 39; & # 39; AS [Database], & # 39; & # 39; & # 39; + @@ SERVERNAME + & # 39; & # 39; & # 39; ACE [Server Name], logins.name Name, logins.type_desc TypeDesc, roles.name RoleName FROM & # 39; + @ databaseName + & # 39; .sys.database_role_members rm INNER JOIN & # 39; + @ databaseName + & # 39 ;. Sys.database_principals roles IN rm.role_principal_id = roles.principal_id INNER JOIN & # 39; + @ databaseName + & # 39; .sys.database_principals logins ON rm.member_principal_id = logins.principal_id & # 39; INSERT IN @ResultTable EXEC (@sql) FETCH NEXT FROM databaseCursor INTO @databaseName FINISH Closecurrent database DEALLOCATE databaseCursor SELECT * FROM @ResultTable;