Sql-server – Generate List of Missing Relationships

sql server

I have two tables, CLUSER and CLUSERRX. CLUSER contains all the employees at my company and CLUSERRX contains a list of all the people in CLUSER with a specific workgroup and all of the users from CLUSER with a similar, but not identical workgroup. The columns for these tables are as follows:

CREATE TABLE [dbo].[CLUSER]
  (
     [LOGONID]   [VARCHAR](15) NOT NULL,
     [WORKGROUP] [VARCHAR](30) NOT NULL,
     PRIMARY KEY CLUSTERED ( [LOGONID] ASC ) WITH (...other settings..., 
       FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY]

CREATE TABLE [dbo].[CLUSERRX]
  (
     [LOGONID] [VARCHAR](15) NOT NULL,
     [RXUSER]  [VARCHAR](10) NOT NULL,
     CONSTRAINT [USERRX_LOGONID] PRIMARY KEY CLUSTERED 
       ( [LOGONID] ASC, [RXUSER] ASC ) WITH (...other settings..., 
     FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY]

I am able to generate a list of every CLUSERRX.LOGONID, CLUSER.WORKGROUP for that LOGONID, CLUSERRX.RXUSER, and CLUSER.WORKGROUP for the RXUSER using the following query:

SELECT u.LOGONID,
       u.WORKGROUP as 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP as 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
         ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
         ON p.LOGONID = r.RXUSER

What I need to do is take the data in CLUSER and find what relationships are missing in CLUSERRX.

Example data:

CLUSER
|-----------|------------|
|  LOGONID  |  WORKGROUP |
|-----------|------------|
| JCURCIO   | 001 USER   |
| TUSER     | 001 SUP    |
| ATEST     | 001 SUP    |
| MPAGE     | 001 User   |
|-----------|------------|

CLUSERRX
|-----------|------------|
|  LOGONID  |  RXUSER    |
|-----------|------------|
| JCURCIO   | TUSER      |
| JCURCIO   | ATEST      |
|-----------|------------|

Since there is no record for MPAGE in CLUSERRX for TUSER or ATEST that is incorrect and I need to display that.

I feel like I need to use something like:

SELECT u.LOGONID,
       u.WORKGROUP AS 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP AS 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
              ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
              ON p.LOGONID = r.RXUSER
WHERE  ( u.LOGONID NOT IN (SELECT u.LOGONID,
                                  u.WORKGROUP AS 'USER WORKGROUP',
                                  r.RXUSER,
                                  p.WORKGROUP AS 'PROVIDER WORKGROUP'
                           FROM   CLUSERRX r
                                  LEFT JOIN CLUSER u
                                         ON u.LOGONID = r.LOGONID
                                  LEFT JOIN cluser p
                                         ON p.LOGONID = r.RXUSER)
         AND p.WORKGROUP NOT IN (SELECT u.LOGONID,
                                        u.WORKGROUP AS 'USER WORKGROUP',
                                        r.RXUSER,
                                        p.WORKGROUP AS 'PROVIDER WORKGROUP'
                                 FROM   CLUSERRX r
                                        LEFT JOIN CLUSER u
                                               ON u.LOGONID = r.LOGONID
                                        LEFT JOIN cluser p
                                               ON p.LOGONID = r.RXUSER) )

But I have no idea how to format it properly to get the information I need. Can anyone provide any insight on what I may be missing, or if I am even in the right direction?

Best Answer

You have to define what you mean with "similar workgroups" and alter the relevant line but this is what I understand from your wording.

It will show all combinations of users from different workgroups (and similar in an arbitrary way, matching the first 3 characters of group name) that do not appear (in either order) in the cluserrx table:

; WITH UserCombinations AS
    ( SELECT r.logonid AS logonid,
             u.workgroup AS User_workgroup,
             p.logonid AS rxuser
             p.workgroup AS Provider_workgroup
      FROM cluser AS u
        JOIN cluser AS p
          ON  u.workgroup < p.workgroup                     -- not identical workgroups
          AND LEFT(u.workgroup, 3) = LEFT(p.workgroup, 3)   -- but similar
    )
SELECT 
    uc.logonid,
    uc.User_workgroup,
    uc.rxuser
    uc.Provider_workgroup
FROM UserCombinations AS uc
WHERE NOT EXISTS
      ( SELECT *
        FROM cluserrx AS rx
        WHERE rx.logonid = uc.logonid  AND  rx.rxuser = uc.rxuser
           OR rx.logonid = uc.rxuser  AND  rx.rxuser = uc.logonid
      ) ;

Related Solutions

Sql-server – Granting permissions only on a set list of objects

I've done this a couple of weeks ago - Created LOGIN, ROLE and USER. Added USER to ROLE and granted explicit permissions to the ROLE itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.

First I created the login:

IF NOT EXISTS 
( 
  SELECT 
    "name" 
  FROM 
    "master"."dbo"."syslogins" 
  WHERE 
    "name" = 'your_login'
)
BEGIN 
  CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF; 
END

I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.

Followed by the user:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE 
    "name" = N'your_username'
)
BEGIN 
  CREATE USER your_username FOR LOGIN your_login;
END

And the role third:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE
    "name" = N'your_role_name'  
)
BEGIN
  CREATE ROLE "your_role_name"; 
END

Finally I added the user to the created role:

EXECUTE sp_addrolemember N'your_role_name', N'your_username';

After that, permissions can be set for each type of object, eg

GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;

You don't have to add denydatareader or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT permission doesn't include the SELECT permission on the same object. ( at least in my case it didn't ;) )

If you have problems executing or selecting from your UDF, check if you have granted all permissions on the referenced objects in the UDF itself!

Update after Cobus' update in OP:

I've executed the queries you provided based on my initial answer, results following:

select * from dbo.testtable

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4 where id = 2

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'. -- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

insert into dbo.testtable (id) values (5)

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?

Sql-server – ORDER BY items must appear in the select list […]

If an alias is used in an ORDER BY it must be used on its own, not inside an expression.

If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias.

So for example

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B

Returns (ordered by alias)

+---+
| B |
+---+
| 1 |
| 2 |
| 3 |
+---+

But

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B + 0

Returns (Ordered by base table column B)

+---+
| B |
+---+
| 3 |
| 2 |
| 1 |
+---+

You can of course just wrap the whole thing in a derived table or CTE.

WITH T AS
(
SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
)
SELECT *
FROM T
ORDER BY CASE WHEN foo = 1 THEN bar END;

Best Answer

Related Solutions

Sql-server – Granting permissions only on a set list of objects

Sql-server – ORDER BY items must appear in the select list […]

Related Question