Sql-server – Return Output from SQL Function using command line SQLCMD

Have you considered SQLCMD?

Here's an article on Using the SQLCMD command line utility.

You can execute adhoc SQL statements, or run .sql scripts.

The following example runs the C:\MyScript.sql script from the command line and outputs the queries and results to a text file located at C:\MyResults.txt.

C:\Windows>sqlcmd -S NOTTINTRA3\SQL2005 -i C:\MyScript.sql -o c:\MyResults.txt -e

This information_schema view (my bold)

Returns one row for each table privilege that is granted to or granted by the current user in the current database.

This doesn't take into account roles etc: it all works on DATABASE_PRINCIPAL_ID which can be seen if you look at the definition of INFORMATION_SCHEMA.TABLE_PRIVILEGES

Personally, I'd just use sys.database_permissions and not bother with the information_schema rubbish. This relies solely on Meta data visibility to filter rows, so you'll see the actual permissions unfiltered by the INFORMATION_SCHEMA.TABLE_PRIVILEGES view which adds a further filter that bollixes you.

For completeness, here is the view. Note the implicit "old style" JOIN :-)

CREATE VIEW INFORMATION_SCHEMA.TABLE_PRIVILEGES
AS
SELECT  
    USER_NAME(p.grantor_principal_id)   AS GRANTOR,
    USER_NAME(p.grantee_principal_id)   AS GRANTEE,
    DB_NAME()                       AS TABLE_CATALOG,
    SCHEMA_NAME(o.schema_id)            AS TABLE_SCHEMA,
    o.name                          AS TABLE_NAME,
    convert(varchar(10), CASE p.type
        WHEN 'RF' THEN 'REFERENCES'
        WHEN 'SL' THEN 'SELECT'
        WHEN 'IN' THEN 'INSERT'
        WHEN 'DL' THEN 'DELETE'
        WHEN 'UP' THEN 'UPDATE'
        END)                        AS PRIVILEGE_TYPE,
    convert(varchar(3), CASE p.state
        WHEN 'G' THEN 'NO'
        WHEN 'W' THEN 'YES'
        END)                        AS IS_GRANTABLE
FROM
    sys.objects o,
    sys.database_permissions p
WHERE
    o.type IN ('U', 'V')
    AND p.class = 1
    AND p.major_id = o.object_id
    AND p.minor_id = 0  -- all columns
    AND p.type IN ('RF','IN','SL','UP','DL')
    AND p.state IN ('W','G')
    AND (p.grantee_principal_id = 0
        OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
        OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID())