Script DB Level Permissions

  • S. Kusen

    SSChampion

    Points: 10859

    Comments posted to this topic are about the item Script DB Level Permissions

  • William Soranno

    SSCommitted

    Points: 1578

    I like the script, it will be useful.

    I have added the following to your code:

    DECLARE

    @sql VARCHAR(2048)

    ,@sort INT

    DECLARE tmp CURSOR FOR

    then your code goes here.

    OPEN tmp

    FETCH NEXT FROM tmp INTO @sql, @sort

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @sql

    FETCH NEXT FROM tmp INTO @sql, @sort

    END

    CLOSE tmp

    DEALLOCATE tmp

    The result is now text that can be copied into another query window and run or saved to a file.

    Thanks for sharing your code.

    Bill

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • S. Kusen

    SSChampion

    Points: 10859

    Thanks for the feedback. That's a good update to it so that it sorts without displaying the sort counter. I knew it could benefit from some updates.

    Thanks!

    Steve

  • choffman

    SSC Rookie

    Points: 47

    Currently it captures stored procedure execute permissions, what needs modified to capture schema execute permissions?

    Thank You

  • S. Kusen

    SSChampion

    Points: 10859

    I believe this would get you the schema permissions you are looking for. I'll have to update my script to include that as well.

    select state_desc+ ' ' + permission_name + ' TO ' + class_desc + '::' + QUOTENAME(SCHEMA_NAME(grantee_principal_id)), perm.state

    from sys.database_permissions AS perm

    inner join sys.schemas s

    on perm.grantee_principal_id = s.schema_i

    WHERE class_desc = 'SCHEMA' --class = 3

    I did some minimal testing with that code and it seems to work as you desire.

    I'm sure there will be other facets missing, so please let me know if you see anything else missing that would be a benefit to the community.

    Thanks,

    Steve

  • karthik.catchme

    SSC Eights!

    Points: 818

    Thanks for the Script. It is very useful. While scripting out the object level permissions, i think, the below line that has been highlighted (Underlined) should have been "QUOTENAME(SCHEMA_NAME(obj.schema_id))". Please correct me if i m wrong

    /*********************************************/

    /********* OBJECT LEVEL PERMISSIONS *********/

    /*********************************************/

    SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

    5 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects

    + CASE

    WHEN cl.column_id IS NULL THEN SPACE(0)

    ELSE '(' + QUOTENAME(cl.name) + ')'

    END

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    5 AS [-- RESULT ORDER HOLDER --]

    FROM

    sys.database_permissions AS perm

  • S. Kusen

    SSChampion

    Points: 10859

    karthik.catchme (11/30/2010)


    Thanks for the Script. It is very useful. While scripting out the object level permissions, i think, the below line that has been highlighted (Underlined) should have been "QUOTENAME(SCHEMA_NAME(obj.schema_id))". Please correct me if i m wrong

    /*********************************************/

    /********* OBJECT LEVEL PERMISSIONS *********/

    /*********************************************/

    SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

    5 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects

    + CASE

    WHEN cl.column_id IS NULL THEN SPACE(0)

    ELSE '(' + QUOTENAME(cl.name) + ')'

    END

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    5 AS [-- RESULT ORDER HOLDER --]

    FROM

    sys.database_permissions AS perm

    Good catch, not sure how I missed that.

    I'll get that fixed and will submit a v2 of this script that includes this fix as well as the enhancement to have it "PRINT" the command output instead of in a two-column grid, and will also include schema level DB permissions per the request of a previous poster.

    Thanks for checking it out.

    Steve

  • RML51

    SSCarpal Tunnel

    Points: 4410

    Thanks for the new v2 version. It looks great! This will be very helpful.

  • serkan.senses

    Valued Member

    Points: 56

    Are there any version to run it in SQL Server 2000? Or what needs to be changed to be able to run in SQL Server 2000?

  • S. Kusen

    SSChampion

    Points: 10859

    Unfortunately, no. I never made one for 2000 and we have so few DB's still in 2000 that it isn't worthwhile to me. There may be someone else on the thread who might have something like that.

    Sorry,

    Steve

  • serkan.senses

    Valued Member

    Points: 56

    Here is the code to list/view/report ALL objects permissions in SQL SERVER 2000. Code will extract DB_name, User Name, Object Name, Object Type, Action and Protect Type.

    use master

    go

    Create View VUserRights

    as

    SELECT top 100 percent

    U.[Name] as UserName

    ,O.Name as ObjectName

    ,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

    --drop view VUserRights

    /*

    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

    -- Reply With Quote

    --select * from sysobjects where name like 'ALS_AppLetterRefNos%'

    --select * from sysdatabases where name like 'ALS%'

    --select * from vuserrights

    CREATE TABLE #t(d SYSNAME, t SYSNAME);

    EXEC sp_msForEachDB 'INSERT #t SELECT ''?'', TABLE_NAME

    FROM [?].INFORMATION_SCHEMA.TABLES

    --WHERE TABLE_TYPE=''BASE TABLE''

    ;';

    select #t.D DBname, V.* from #t right join vuserrights V on #t.t=V.Objectname

    --select * from vuserrights

    --select * from vuserrights where Oname NOT EXISTS(select #t.D, #t.t, V.* from #t right join vuserrights V on #t.t=V.Oname)

    --SELECT * FROM #t ORDER BY d,t;

    --select * from INFORMATION_SCHEMA.TABLES

    DROP TABLE #t;

  • arun.gopinath

    SSC Enthusiast

    Points: 161

    I'm aware this is an old post, But I feel there may be a small error in the schema script section.

    that section will result in an out put something like this

    GRANT SELECT TO SCHEMA::[HumanResources] (The syntax may be wrong, more over it fetches the first schema and in the subsequent order- and not the reuired schema)

    The script may have to modified this way

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name --CONNECT, etc

    + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

    + QUOTENAME(SCHEMA_NAME(major_id))

    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    10 AS [-- RESULT ORDER HOLDER --]

    from sys.database_permissions AS perm

    inner join sys.schemas s

    on perm.grantee_principal_id = s.schema_id

    WHERE class = 3

    Now the result returns as (For e.g)

    GRANT SELECT ON SCHEMA::[Sales] TO [ag]

    By the way thank you for consolidating this awesome script 🙂

    -Arun

  • S. Kusen

    SSChampion

    Points: 10859

    aruopna (5/14/2012)


    I'm aware this is an old post, But I feel there may be a small error in the schema script section.

    that section will result in an out put something like this

    GRANT SELECT TO SCHEMA::[HumanResources] (The syntax may be wrong, more over it fetches the first schema and in the subsequent order- and not the reuired schema)

    The script may have to modified this way

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name --CONNECT, etc

    + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

    + QUOTENAME(SCHEMA_NAME(major_id))

    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    10 AS [-- RESULT ORDER HOLDER --]

    from sys.database_permissions AS perm

    inner join sys.schemas s

    on perm.grantee_principal_id = s.schema_id

    WHERE class = 3

    Now the result returns as (For e.g)

    GRANT SELECT ON SCHEMA::[Sales] TO [ag]

    By the way thank you for consolidating this awesome script 🙂

    -Arun

    Hi Arun,

    Thanks for your feedback. I suppose a lot of people (including myself) do not use schema-based permissions. Do you have an example that I can establish schema level permissions and test out of the update?

    I ran the following code, but I cannot get it to come back as part of the DB level schema permissions.

    GRANT SELECT ON SCHEMA::[test_perms_schema] TO [test_perms_login]

    Any example you can provide that I can re-test the code would certainly help. I will then get the updated script posted to include your fix. I truly appreciate it, but want to be sure this time around.

    Thanks, and have a great day.

    Steve

  • arun.gopinath

    SSC Enthusiast

    Points: 161

    Hello Steve,

    here's an example (probably sketched with too much detail :-))

    --Create a Login and a corresponding user

    USE master

    GO

    CREATE LOGIN aG WITH PASSWORD = 'somepwd',CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

    USE AdventureWorks

    GO

    CREATE USER aG FOR LOGIN aG

    GO

    --Let us try authenticating as aG and see the results

    USE AdventureWorks

    GO

    EXECUTE AS USER = 'aG'

    SELECT TOP 10 * FROM Sales.Customer

    /*Msg 229, Level 14, State 5, Line 2

    The SELECT permission was denied on the object 'Customer', database 'AdventureWorks', schema 'Sales'.*/

    REVERT

    --Grant explicit privileges

    GRANT SELECT ON SCHEMA::[Sales] TO [aG]

    GO

    --Authenticate again using aG

    EXECUTE AS USER = 'aG'

    SELECT TOP 10 * FROM Sales.Customer

    --You should be able to select the top 10 records. (just to make sure the permissions are granted)

    --Now Run the scripts

    REVERT

    --Existing script

    USE AdventureWorks

    GO

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name --CONNECT, etc

    + SPACE(1) + 'TO' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

    + QUOTENAME(SCHEMA_NAME(grantee_principal_id))

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    10 AS [-- RESULT ORDER HOLDER --]

    from sys.database_permissions AS perm

    inner join sys.schemas s

    on perm.grantee_principal_id = s.schema_id

    WHERE class = 3

    -- SQL STATEMENTS -- -- RESULT ORDER HOLDER --

    ----------------------------------------------------------------------------

    --GRANT SELECT TO SCHEMA::[HumanResources] 10

    --Updated script

    USE AdventureWorks

    GO

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name --CONNECT, etc

    + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

    + QUOTENAME(SCHEMA_NAME(major_id))

    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    10 AS [-- RESULT ORDER HOLDER --]

    from sys.database_permissions AS perm

    inner join sys.schemas s

    on perm.grantee_principal_id = s.schema_id

    WHERE class = 3

    -- SQL STATEMENTS -- -- RESULT ORDER HOLDER --

    ----------------------------------------------------------------------------

    --GRANT SELECT ON SCHEMA::[Sales] TO [ag] 10

    --You can still do the same thing using GUI.

    --Navigate through Instance name > databasename > Security > Schemas > Properties > Permission tab

    --Grant/revoke explict privileges. Run the scripts again.

    --I have verified with 'WITH GRANT OPTION'; also for Windows authenticated login, works fine for me.

    Cheers!

  • michael.ansaldi@gmail.com

    SSCommitted

    Points: 1641

    S. Kusen, thank you for this helpful script!

Viewing 15 posts - 1 through 15 (of 58 total)

You must be logged in to reply to this topic. Login to reply