Wednesday, October 20, 2010

Script : Sql logins without a default database

How to find sql logins that do not have a default database set?

SELECT * FROM sys.server_principals 
WHERE [type] IN ('U', 'G', 'S','C') ]
AND default_database_name IS NULL

NB : Column TYPE_DESC provides the meaning of TYPE.

If a database has been deleted, a login could still have it's default database set to one that has been removed. To find these ...

SELECT * FROM sys.server_principals 
WHERE [type] IN ('U', 'G', 'S','C') 
AND [default_database_name] NOT IN (SELECT name FROM sys.databases)

No comments: