I was trying to list all databases that are mapped to a login in sql server using T-SQL without creating a temp or any type of table. Any help will be appreciated.
The simplest way is inside SQL Server Management Studio (SSMS). Go to the Security tab, right click the user name, and select the option User Mapping. Wait a bit, and a list of all DBs on the server comes up, with a check mark next to the ones that the user has access to.
If you also want to know what roles the user has in each of the DBs, you can click on the database name, and the database role membership will show in the bottom window.
This is one way, find all with "CONNECT SQL" permission of type "SQL_LOGIN" or "WINDOWS_LOGIN". Searching can be done with the final query of the temp table.
The section with
select name from master.sys.databases where database_id > 4 is to only display the non-system databases. For all, take the where off.
Oversight: Corrected to appropriate view. Database level now, was server level.
declare @dbName varchar(128),@s varchar(max) if object_id('tempdb..#userMapping','U') is not null drop table #userMapping; create table #userMapping ( [id] int identity(1,1) not null ,[user_name] varchar(256) ,[permission_name] varchar(256) ,[database_name] varchar(256) ); declare x cursor fast_forward local for select name from master.sys.databases where database_id > 4 open x; fetch next from x into @dbName; while @@fetch_status = 0 begin set @s = ' use ' + quotename(@dbName) + '; select iif(p.name = ''dbo'',(select suser_sname(d.owner_sid) from sys.databases d where d.name = '''' + db_name(pe.major_id) + ''''),p.name) user_name ,pe.permission_name ,db_name(pe.major_id) database_name from sys.database_principals p join sys.database_permissions pe on pe.grantee_principal_id = p.principal_id where permission_name = ''CONNECT'' and type_desc in (''SQL_USER'',''WINDOWS_USER'');'; insert into #userMapping (user_name, permission_name, database_name) exec(@s); fetch next from x into @dbName; end close x; deallocate x; select * from #userMapping GO