Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chethana
Starting Member
6 Posts |
 Posted - 2010-04-28 : 08:48:51
|
| Hey guys Help me its damm urgentI need to Get the list of logins and the respective username in the databasesthe result should be Login_name,Database_name,User_nameThanks a lot in advance |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-28 : 08:52:39
|
| What version of SQL Server?------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
chethana
Starting Member
6 Posts |
Posted - 2010-04-28 : 09:21:27
|
| Its sql server 2005 |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-28 : 10:10:06
|
| The sys.database_principals in each database contains a list of each databases' users. You can join that to the master.sys.login_token to get their login names.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
chethana
Starting Member
6 Posts |
Posted - 2010-04-28 : 10:59:56
|
| sys.login_token table is not listing the sql server login i.e i have a login Log1 which is having access to a,b,c,d databases i need to list down all in which i am not getting the query exactly. |
|
|
|
chethana
Starting Member
6 Posts |
Posted - 2010-04-28 : 12:11:12
|
| guys thanks for the efforts some how i got the needed and here is the desired script.Declare @Login table ( Database_name Varchar(1000),Login_Name varchar(2000),Username varchar(200))Insert into @Loginexec sp_msforeachdb @command1= 'USE ?; select db_name(), ss.name ,dp.name from sys.syslogins ss left join sys.database_principals dp on ss.sid = dp.sid where dp.type not like ''R'''Select * from @Login where Database_name not in ( 'master','model','msdb','tempdb') |
|
|
|
|
|
|
|
|
Login listing