I saw a post recently that had query that looked like this:

select a.*,name, b.*
 from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT'
and
b.grantee_principal_id = a.principal_id

Ugly to read, at least to me, and in a poorly written format. The table, table format isn’t ANSI compliant and isn’t recommended. So I did this:

A little better, and easier to read, but not great.

SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a ,
        sys.database_permissions b
WHERE   permission_name = 'INSERT'
        AND b.grantee_principal_id = a.principal_id

However now I can make a few quick edits. Remove the comma between tables and add “INNER JOIN” and then move the AND clause up to an ON clause to give me this:

SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
    ON b.grantee_principal_id = a.principal_id
WHERE   permission_name = 'INSERT'

Much better, and easier to read.

Filed under: Blog Tagged: Red Gate, SQL Prompt, syndicated, T-SQL