Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Question Unanswered: Query permissions on objects for roles?

    How do I query the sys tables in a database to find what objects have what permissions for a given role? A SELECT statement is desired, and I'd like to avoid using sp_ stored procedures, but mention of a few to look at would be appreciated.

    Thanks to anyone who can help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is part of a script I wrote for restoring server and database configuration. It creates an sql script for restoring user and role permissions. It uses the bitwise & operator to decode values in the sypermissions table. If it's not exactly what you are looking for, you can deconstruct it to see how to pull the data you need.

    blindman
    --------------------
    set nocount on
    select '--OBJECT LEVEL PERMISSIONS'
    select 'GRANT SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 1 & syspermissions.actadd > 0
    go
    select 'DENY SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 1 & syspermissions.actmod > 0
    go
    select 'GRANT UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 2 & syspermissions.actadd > 0
    go
    select 'DENY UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 2 & syspermissions.actmod > 0
    go
    select 'GRANT REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 4 & syspermissions.actadd > 0
    go
    select 'DENY REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 4 & syspermissions.actmod > 0
    go
    select 'GRANT INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 8 & syspermissions.actadd > 0
    go
    select 'DENY INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 8 & syspermissions.actmod > 0
    go
    select 'GRANT DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 16 & syspermissions.actadd > 0
    go
    select 'DENY DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 16 & syspermissions.actmod > 0
    go
    select 'GRANT EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 32 & syspermissions.actadd > 0
    go
    select 'DENY EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
    from sysusers owners
    inner join sysobjects on owners.uid = sysobjects.uid
    inner join syspermissions on sysobjects.id = syspermissions.id
    inner join sysusers on syspermissions.grantee = sysusers.uid
    where 32 & syspermissions.actmod > 0
    go

  3. #3
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    I would suggest looking at sp_helprotect , you may be able to borrow code from it and my code below to do what you want. Also, look at the view INFORMATION_SCHEMA.TABLE_PRIVILEGES in master DB.

    Tim S

    Here's my sproc to script permissions:

    CREATE proc dbo.adm_script_permissions
    (
    @name ncharacter varying(776) = NULL,
    @username sysname = NULL,
    @grantorname sysname = NULL,
    @permissionarea character varying(10) = 'o s'
    )AS
    BEGIN

    SET NOCOUNT ON

    CREATE Table #tmp_protect_data ( OwnerName sysname NOT Null, ObjectName sysname NOT Null, GranteeName sysname NOT Null,
    GrantorName sysname NOT Null, ProtectType char(10) Null, ActionName varchar(20) Null, ColumnName sysname Null)

    SET @username = PARSENAME ( @username , 1 ) -- Remove []

    insert into #tmp_protect_data (OwnerName, ObjectName, GranteeName, GrantorName, ProtectType, ActionName, ColumnName )
    exec sp_helprotect @name = @name, @username = @username, @grantorname = @grantorname, @permissionarea = @permissionarea

    SELECT RTRIM(ProtectType) + ' ' + ActionName +
    CASE
    WHEN ColumnName IN ('.','(All+New)','(All)','(New)') THEN ''
    ELSE ' (' + ColumnName + ')'
    END +
    ' ON ' + OwnerName + '.' + ObjectName + ' TO ' + GranteeName + char(13)+char(10)
    FROM #tmp_protect_data
    WHERE ColumnName IS NOT NULL AND -- ColumnName is NULL on deleted columns
    OwnerName <> 'INFORMATION_SCHEMA' AND ObjectName NOT LIKE 'dt[_]%' AND ObjectName NOT IN ('dtproperties')
    ORDER BY OwnerName + '.' + ObjectName, GranteeName, ActionName, RTRIM(ProtectType)

    DROP Table #tmp_protect_data

    SET NOCOUNT OFF

    END
    Last edited by TimS; 07-13-03 at 02:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •