0

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.

1
1

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.

1
  • Thank you for your response. My requirement is using a T-SQL scrip. – AiB Apr 3 '19 at 16:54
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.