Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2002
    Posts
    71

    Unanswered: script db object permissions

    I'm looking into processes to move to using database specific roles and migrating specifically assigned user permissions to these roles. I have made progress on listing permissions in the public role and assigning these same permissions to the new role that I have created for the given database. However, when I inspect permissions on tables and other objects, permissions have also been specifically granted for application users in the db. My ojective is to script out the permissions against the database objects (mainly user tables) and then use the output to assign the same permissions to the appropriate role instead. However, I'm having difficulty finding which system table stores the table and column level permissions.

    If I do select * from sysobjects, I can see the user tables. However, I can't find the related tables that store user object permissions. For example, sysprotects doesn't store this info and, if I do an inner join on 'id' between these two tables, I don't see any user tables. Where do I look? Are there any useful 'already done' scripts/procs around for these purposes?

    Regards,

    Clive

  2. #2
    Join Date
    Apr 2004
    Posts
    101
    Try this one..
    sp_helprotect
    sskris

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    well
    i dont have an sql server with me now, but you could create a query between the sysobjects, sysusers, and sysprotects table and you could interpret the action and the protecttype columns
    [Books Online] sysusers
    [Books Online] sysobjects
    [Books Online] sysprotects

    if you want a quick way to view system tables in a graphical format you should download systbl.chm which is also available in your sql server 2000 resource kit.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    /*
    This script creates a view to display users and objects that they have permissions for
    and the permissions that are set
    */

    use master
    go
    Create View VUserRights
    as
    SELECT top 100 percent
    U.[Name] as UName
    ,O.Name as OName
    ,case xtype
    when 'S' then 'System Table'
    when 'P' then 'Stored Procedure'
    when 'C' then 'Check Constraint'
    when 'D' then 'Default'
    when 'F' then 'Foreign Key'
    when 'L' then 'Log'
    when 'FN' then 'Scalar Function'
    when 'IF' then 'Inlined Table-Function'
    when 'PK' then 'PRIMARY KEY'
    when 'RF' then 'Replication Filter Stored Procedure'
    when 'S' then 'System Table'
    when 'TF' then 'Table Function'
    when 'TR' then 'Trigger'
    when 'U' then 'User Table'
    when 'UQ' then 'UNIQUE Constraint'
    when 'V' then 'View'
    when 'X' then 'Extended Stored Procedure'
    else cast(xtype as varchar(30))
    end as XType

    ,Case p.[action]
    When 26 then 'REFERENCES'
    When 178 then 'CREATE FUNCTION'
    When 193 then 'SELECT'
    When 195 then 'INSERT'
    When 196 then 'DELETE'
    When 197 then 'UPDATE'
    When 198 then 'CREATE TABLE'
    When 203 then 'CREATE DATABASE'
    When 207 then 'CREATE VIEW'
    When 222 then 'CREATE PROCEDURE'
    When 224 then 'EXECUTE'
    When 228 then 'BACKUP DATABASE'
    When 233 then 'CREATE DEFAULT'
    When 235 then 'BACKUP LOG'
    When 236 then 'CREATE RULE'
    Else cast([Action] as varchar(20))
    End as 'Action'

    ,Case p.protecttype
    When 204 Then 'GRANT_W_GRANT'
    When 205 Then 'GRANT'
    When 206 Then 'REVOKE'
    Else cast(protecttype as varchar(20))
    end as ProtectType

    FROM sysusers U join sysprotects P
    on u.uid = P.uid
    Join sysobjects O
    on P.id = O.id
    where xtype <>'s'
    order by U.uid ASC, O.xtype Desc

    /*
    Here are some calling statements
    --2 is an oracle trick that i learned to
    create a permissions assignment statement from exisiting metadata
    */

    GO
    --1
    select * from vuserrights
    Go
    --2
    select Protecttype + ' ' +
    Action + ' ON ' +
    Oname
    -- +'('+ Xtype+')'
    + ' TO ' + Uname from vuserRights

  5. #5
    Join Date
    Nov 2002
    Posts
    71
    Thank you for the info. Nice View by the way.

    I ended up writing a script that used sp_helprotect. It dumps out the permissions for a given user to a temp table and then I cursor through the temp table to 'grant' the permissions to a new role. eg. give me the Public permissions that have been granted against objects in this db and copy them to a new db role. Also, I can revoke the permsissions on Public as an option. So, I simply do:-

    set @GrantSQL ='Grant ' + @action + ' on [' + @Obj + '] to ' + @Role
    exec(@GrantSQL)

    However, although the above works fine on the db's I'm working on, I suspect that it wouldn't work with regard to database that have column level permssions. I tried setting a column level permission to see how my script would handle it. sp_helprotect reports the column level permssion but I haven't got around to modifiying my script to deal with it properly - currently it migrates the column level permssion in the source user/role to the target role as a table level permisson. In the loop that cursors through the temp table containing the sp_helprotect output, I presumably need to detect a column level permssion and branch to execute a grant statement that will apply a column level permission? Haven't had time to research this yet.

    Thanks again,

    Clive

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are comfortable with VB or VC, then SQL-DMO offers exactly what you want.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Posts
    71
    Pat,

    I am comfortable with VB but haven't doen anything with dmo yet. Got any examples?

    Cheers,

    Clive

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just follow the link. There are zillions of examples scattered on the appropriate pages.

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Zillions ?
    it's more like a Quintillion.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Ruprect
    Zillions ?
    it's more like a Quintillion.
    Ok, ok, ok... So what's a few zeros here and there between friends, eh?

    -PatP

Posting Permissions

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