0

I am trying to figure out how to get this figured out.

Problem:

Users of inactive customers are receiving scheduled maintenance notifications

Indicators:

  • Organizations have a custom binary field to indicate if they are Active
  • Users have two binary fields to indicate if they can access the system

Solution:

  • Identify any users that have their Active and/or CanLogIn values set to 1 AND
  • Are associated by OrganizationID to an Organization with the custom Active field set to 0 (zero)
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29 '17 at 9:32
1

You can find the logins on an instance by querying sys.server_principals and checking the is_disabled column to know if they are still useable.

SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type IN ('U','S')

The other notes you have in your question though indicate that this request is based on an application table. You state things like Active and/or CanLogIn values set to 1 and associated by OrganizationID to an Organization. All of that would tell me this is custom tables within an application database. Which unless you provide the table definition(s) there is not much we can do to help.

0
0

I hope below will help with assumption that ID and OrganizationID is primary key.

Select Org.Name, Usr.UserId, Usr.OrganizationID, Usr.Active, Usr.Canlogin, Org.[Custom Field]     
From Organizations Org   
INNER JOIN  
Users Usr  
On Org.ID = USR.OrganizationID  
Where Usr.Active = 1  Usr.Canlogin = 1  
and Org.[Custom Field] = 0

You still have to change columns names as per your table schema.

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.