0

I am working on converting a PostgreSQL query to SQL server and trying to understand what exactly the query below is doing . I understand the joins but the rest is not clear at all . Could anyone please help me out here .

select c.name 
 FROM CNT c
 LEFT JOIN shares s ON
 c.id = s.id AND s.username::name = "current_user"()
  WHERE c.username::name = "current_user"() OR 'admin'::text = (( SELECT user_role_privs.granted_role
          FROM user_role_privs
          WHERE user_role_privs.granted_role = 'admin'::text)) OR s.username IS NOT NULL AND s.grantee_username::text <> ''::text
user3844877
  • 493
  • 4
  • 9
  • 18

1 Answers1

0

I believe the query below is the SQL Server equivalent. I added the dbo schema name and statement terminator to illustrate best practices but that is not technically required.

SELECT  c.name
FROM    dbo.CNT AS c
        LEFT JOIN dbo.shares s ON c.id = s.id
                              AND s.username = CURRENT_USER
WHERE   c.username = CURRENT_USER
        OR IS_MEMBER(N'admin') = 1
        OR s.username IS NOT NULL
        AND s.grantee_username <> '';
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Thanks for this but `dbo.user_role_privs` doesn't seem to be a valid object in SQL Server . It looks like a postgreSQL system table . – user3844877 Sep 30 '14 at 11:56
  • I changed to use the IS_NUMBER function, which returns 1 if the current user is a member of the specified database role. – Dan Guzman Sep 30 '14 at 12:04