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