Below 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


How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.