RE: Need help with query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 10/11/04 

Date: Mon, 11 Oct 2004 10:13:04 -0700

Try,

select
        a.login
from
        servers a
        inner join
        (
        select distinct
                serverName, dbName
        from
                servers
        )s
        on a.serverName = s.serverName and a.dbName = s.dbName
group by
        a.login
having
        count(*) <> (select count(*) from (select distinct serverName, dbName from
servers) as s1)
go

you should add DRI and constraints also.

Example:

CREATE TABLE Servers (
ServerName sysname NOT NULL,
DBName sysname NOT NULL,
Login sysname NOT NULL,
constraint pk_servers primary key nonclustered (ServerName, DBName, Login)
)
GO

AMB

"DBA72" wrote:

> I have a table containing listings of servers, databases, and users. Each
> server has the same databases but not all the same users exist in the same
> databases. I want to determine which users are not in all identical databases
> across the servers. Here is the table and data:
> IF OBJECT_ID('Servers') IS NOT NULL DROP TABLE Servers
> GO
> CREATE TABLE Servers (ServerName sysname NOT NULL, DBName sysname NOT NULL,
> Login sysname NOT NULL)
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server1','DB1','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server1','DB1','Login2')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server2','DB1','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server2','DB1','Login2')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server3','DB1','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server3','DB1','Login3')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server4','DB1','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server4','DB1','Login2')
> GO
> --
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server1','DB2','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server1','DB2','Login2')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server2','DB2','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server2','DB2','Login2')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server3','DB2','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server3','DB2','Login3')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server4','DB2','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server4','DB2','Login2')
> GO
> --
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server1','DB3','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server1','DB3','Login2')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server2','DB3','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server2','DB3','Login2')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server3','DB3','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server3','DB3','Login3')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server4','DB3','Login1')
> GO
> INSERT Servers (ServerName, DBName, Login)
> VALUES('Server4','DB3','Login2')
> GO
>

Relevant Pages

  • Need help with query
    ... I have a table containing listings of servers, databases, and users. ... CREATE TABLE Servers (ServerName sysname NOT NULL, DBName sysname NOT NULL, ... INSERT Servers (ServerName, DBName, Login) ...
    (microsoft.public.sqlserver.programming)
  • Re: Linux authentication via AD
    ... Primarily I need to integrate Linux ... servers, but I do have a few OpenBSD servers. ... > a way to do this under older AIX) allows people to login authenticating ... > text passwords authenticate to the Windows Password Server as well. ...
    (comp.os.linux.security)
  • Re: Update site?
    ... The reason I needed to login from the same machine is - ... > Users by default are NOT allowed to logon to servers. ... You can change this in the Domain Security Policy I ... there open Domain Controller security policy. ...
    (microsoft.public.exchange.admin)
  • LDAP password authentication/modification schemes
    ... We are considering using LDAP to replace NIS+. ... SSL to encrypt the client to LDAP server transactions. ... authentication would be required for login, ... understand the servers responses and/or provide the appropriate responses to ...
    (comp.sys.hp.hpux)
  • Re: UNABLE TO CONNECT???
    ... realm logins have been nerfed!!!!!!!!!!! ... > login servers ftw! ... own login access? ... on their servers. ...
    (alt.games.warcraft)