We help IT Professionals succeed at work.

listing SQL login names of valid databases

DevSupport
DevSupport asked
on
85 Views
Last Modified: 2017-03-07
I am trying to list the logins in MS SQL database from sys.server_principals table.

But I am trying to get only those logins which have valid databases, I can see that the sys.server_principals table have logins which are associated to old default databases which does not exist.

I am trying to make something like:

SELECT * FROM sys.server_principals where default_database_name = (select name from sys.databases) AND type_desc = 'SQL_LOGIN'

I would really appreciate if you could help me with a query.

Thanks
DevSupport
Comment
Watch Question

Author

Commented:
Does this sound correct?

select * from sys.server_principals A where A.default_database_name IN (select name from sys.databases) AND A.type_desc = 'SQL_LOGIN'
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.