4

How can I list all users which can connect to my sql server database? Now able to find any sql command. I tried few links available on the internet but none of them worked.

Some commands that I tried 

SELECT * FROM sys.sql_logins;

SELECT * FROM sys.sysusers;

select * from sys.sysusers
SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE [type_desc] = 'SQL_USER'

I also looked in azure portal but did not find any users information.

I am able to connect to sql database which connection string where I am providing userid and password. Now I am not able to find that userid in sql server.

connection string looks like

value="Server=server,1433;Initial Catalog=DB1;Persist Security Info=False;User ID=user1;Password=pass1;Encrypt=True;Connection Timeout=30;"
anand
  • 11,071
  • 28
  • 101
  • 159
  • 1
    Please edit your question and provide details of what you have tried so far? Also tell us what was the expected result and what did you actually get when you tried. – Gaurav Mantri Sep 19 '17 at 06:16
  • Instead of connecting to `DB1`, try connecting to `master` database and then execute `SELECT * FROM sys.sql_logins;`. I just tried it with my database and it worked for me. – Gaurav Mantri Sep 19 '17 at 07:08
  • I am seeing some username but that is not the which I am referring in the connection string. – anand Sep 19 '17 at 07:18
  • Weird. I see the name perfectly fine. The name I got did not include the database server name. So if you're connecting using something like `username@servername`, you should see `username` only when you execute the command. BTW, I tried it in SSMS and not through code but I don't think that will make any difference. – Gaurav Mantri Sep 19 '17 at 07:20

1 Answers1

3

Run this query on master:

SELECT A.name as userName, B.name as login, B.Type_desc, default_database_name, B.* 
FROM sys.sysusers A 
    FULL OUTER JOIN sys.sql_logins B 
       ON A.sid = B.sid 
WHERE islogin = 1 and A.sid is not null

Run this on user databases:

SELECT DB_NAME(DB_ID()) as DatabaseName, * FROM sys.sysusers
slugster
  • 49,403
  • 14
  • 95
  • 145
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30