Tuesday, June 05, 2007

Checking database permissions in SQL Server 2000

I recently had to write a script to confirm that correct database permissions had been set on a database. Here's how I did it. Hopefully the comments will make it clear what's going on.


--- This uses the syspermissions.actadd column.
--- This is an undocumented column and is not guaranteed to work in future versions of SQL Server.

SELECT 'ATTENTION:' AS Type, 'Any entries below have not been granted adequate permissions' AS Name

UNION

-- This query will return all user tables that xxxxxx has not granted SELECT, INSERT, UPDATE & DELETE permissions

SELECT 'TABLE', o.name
FROM sysobjects o
WHERE o.xtype = 'U'
AND o.name != 'dtproperties'
AND o.name NOT IN (
SELECT o.name
FROM syspermissions p, sysobjects o, sysusers u
WHERE p.id = o.id
AND u.uid = p.grantee
AND u.name = 'xxxxxx'
AND o.xtype = 'U' --User Table
AND p.actadd = 27 ) -- = 1 (SELECT) + 2 (UPDATE) + 8 (INSERT) + 16 (DELETE)

UNION

-- This query will return any view that xxxxxx has not been granted SELECT permission
-- If other permissions have also been granted they will be ignored

SELECT 'VIEW', o.name
FROM sysobjects o
WHERE o.xtype = 'V'
AND o.category = 0
AND o.name NOT IN (
SELECT o.name
FROM syspermissions p, sysobjects o, sysusers u
WHERE p.id = o.id
AND u.uid = p.grantee
AND u.name = 'xxxxxx'
AND o.xtype = 'V' --View
AND (p.actadd & 1) = 1 ) -- BITWISE check to confirm that the SELECT permission is set

UNION

-- This query will return any stored procedure that xxxxxx has not been granted EXEC permission

SELECT 'STORED PROCEDURE', o.name
FROM sysobjects o
WHERE o.xtype = 'P'
AND o.name NOT LIKE 'dt_%'
AND o.name NOT IN (
SELECT o.name
FROM sysobjects o, syspermissions p, sysusers u
WHERE o.id = p.id
AND p.grantee = u.uid
AND u.name = 'xxxxxx'
AND o.xtype = 'P' --Stored Procedure
AND p.actadd = 32 )-- 32 = EXEC

UNION

-- This query will return any functions that xxxxxx has not been granted EXEC permission

SELECT 'FUNCTION', o.name
FROM sysobjects o
WHERE (o.xtype = 'FN' OR xtype = 'IF')
AND o.name NOT IN (
SELECT o.name
FROM sysobjects o, syspermissions p, sysusers u
WHERE o.id = p.id
AND p.grantee = u.uid
AND u.name = 'xxxxxx'
AND (o.xtype = 'FN' OR xtype = 'IF') --Scalar Function or In-lined table-Function
AND p.actadd = 32 )-- 32 = EXEC
ORDER BY 1, 2

1 comment:

  1. Anonymous3:02 pm

    --- continued
    OPEN curObj;
    WHILE(1=1)
    BEGIN
    FETCH NEXT FROM curObj INTO @role, @permTarget, @permName, @objXType, @objName, @hasAllPerms;
    IF (@@fetch_status <> 0)
    BEGIN
    IF @count IS NOT NULL PRINT '-- ' + CONVERT(varchar, ISNULL(@skippedCount,0)) + '/' + CONVERT(varchar, @count) + ' skipped';
    BREAK;
    END

    IF @prevRole <> @role
    BEGIN
    IF @count IS NOT NULL PRINT '-- ' + CONVERT(varchar, ISNULL(@skippedCount,0)) + '/' + CONVERT(varchar, @count) + ' skipped';
    PRINT @LB + '-- ----------------' + @LB + '-- Grants to ' + @role + @LB + ' ';
    SELECT @skippedCount = 0, @count = 0;
    END

    SET @count = @count + 1;

    IF (@hasAllPerms = 0 OR @force_grant = 1)
    BEGIN
    IF @permTarget = 'func'
    BEGIN
    IF (@objXType = 'TF' AND @permName = 'EXECUTE') SET @permName = 'SELECT'; -- uses 'SELECT' for Table-Function
    SET @permTarget = @permTarget + ' ' + ISNULL(@objXType,'');
    END

    SET @sql = 'GRANT ' + @permName + ' ON dbo.' + @objName + ' TO ' + @role;
    IF @preview = 0
    BEGIN
    PRINT @permTarget + ' ' + @sql + ' :';
    EXEC(@sql);
    END
    ELSE IF @pvw_script_ready = 0
    PRINT @permTarget + ' ' + @sql ;
    ELSE -- preview, script ready
    PRINT @sql + ';';
    END
    ELSE
    BEGIN
    SET @skippedCount = @skippedCount + 1;
    IF (@preview = 1 AND @pvw_print_skipped = 1)
    PRINT '-- ' + @permTarget + ' already granted to ' + @permName + ' on ' + @objName + ' : skipped';
    END

    SET @prevRole = @role;
    END
    CLOSE curObj;
    DEALLOCATE curObj;

    ReplyDelete

I get a lot of comment spam :( - moderation may take a while.