T-SQL script to check user access on multiple SQL databases
MSSQL SQL on Linux, SQL Server database accessBelow query can be used to check user access on multiple databases in SQL Server.
declare @UsrPerm_sql varchar(3000)
declare @UsrPerm_table table ( ServerName varchar(200), DBName varchar(200), UserName varchar(250), LoginType varchar(500), DatabaseRole varchar(200))
set @UsrPerm_sql='select @@servername, ''?'' as DBName,a.name as Name,a.type_desc as LoginType,user_name(b.role_principal_id) as DatabaseRole from ?.sys.database_principals a
left outer join ?.sys.database_role_members b on a.principal_id=b.member_principal_id
where a.name="admin"' --change user name here
insert @UsrPerm_table
exec sp_MSforeachdb @command1=@UsrPerm_sql
select* from @UsrPerm_table order by DBName
Output:
