For Multiple DB

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 14, 2018 at 12:48 pm

  #1983274

  Might want to try it this way:
  DECLARE @command AS varchar(2000) = '
USE ?

  PRINT ''--=========================================================================='';
PRINT ''USE ?'';
PRINT ''--=========================================================================='';

  DECLARE @note AS varchar(2048), @id AS int;
DECLARE C_sql CURSOR FOR
    SELECT
        CASE
            WHEN perm.state <> ''W'' THEN perm.state_desc
            ELSE ''GRANT''
        END +
        SPACE(1) + perm.permission_name +
        SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default +        --TO <user name>
        CASE
            WHEN perm.state <> ''W'' THEN SPACE(0)
            ELSE SPACE(1) + ''WITH GRANT OPTION''
        END
        AS [SQL ],
        1 AS status
    FROM sys.database_permissions AS perm
        INNER JOIN sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
    WHERE [perm].[major_id] = 0
        AND [usr].[principal_id] > 2
        AND [usr].[type] = ''U''
    UNION
    SELECT    '''', 2
    UNION
    SELECT '''', 3
    UNION
    SELECT
        CASE
            WHEN z.state <> ''W'' THEN z.state_desc
            ELSE ''GRANT''
        END +
        SPACE(1) + z.permission_name +
        SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
        QUOTENAME(SCHEMA_NAME(major_id)) +
        SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
        CASE
            WHEN z.state <> ''W'' THEN SPACE(0)
            ELSE SPACE(1) + ''WITH GRANT OPTION''
        END AS [test],
        4
    FROM sys.database_permissions AS z
        INNER JOIN sys.schemas AS s
            ON z.major_id = s.[schema_id]
        INNER JOIN sys.database_principals AS x
            ON z.grantee_principal_id = x.principal_id
    WHERE class = 3;        --class 3 = schema

  OPEN C_SQL;
FETCH NEXT FROM C_SQL INTO @note, @id
WHILE @@FETCH_STATUS = 0
    BEGIN
    IF LTRIM(RTRIM(@note)) <> ''''
        BEGIN
        PRINT @note;
        END;
    FETCH NEXT FROM C_SQL INTO @note, @id
    END;

  CLOSE C_SQL;
DEALLOCATE C_SQL;';

  EXEC sp_MSforeachdb @command;

  EDIT: Fix it to actually be a runnable script.result.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Nita Reddy

  SSCarpal Tunnel

  Points: 4177

  More actions

  March 14, 2018 at 1:00 pm

  #1983275

  sgmunson - Wednesday, March 14, 2018 12:48 PM

  Might want to try it this way:
  DECLARE @command AS varchar(2000) = '
  USE ?

  PRINT ''--=========================================================================='';
  PRINT ''USE ?'';
  PRINT ''--=========================================================================='';

  DECLARE @note AS varchar(2048), @id AS int;
  DECLARE C_sql CURSOR FOR
      SELECT
          CASE
              WHEN perm.state <> ''W'' THEN perm.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + perm.permission_name +
          SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default +        --TO <user name>
          CASE
              WHEN perm.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END
          AS [SQL ],
          1 AS status
      FROM sys.database_permissions AS perm
          INNER JOIN sys.database_principals AS usr
              ON perm.grantee_principal_id = usr.principal_id
      WHERE [perm].[major_id] = 0
          AND [usr].[principal_id] > 2
          AND [usr].[type] = ''U''
      UNION
      SELECT    '''', 2
      UNION
      SELECT '''', 3
      UNION
      SELECT
          CASE
              WHEN z.state <> ''W'' THEN z.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + z.permission_name +
          SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
          QUOTENAME(SCHEMA_NAME(major_id)) +
          SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
          CASE
              WHEN z.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END AS [test],
          4
      FROM sys.database_permissions AS z
          INNER JOIN sys.schemas AS s
              ON z.major_id = s.[schema_id]
          INNER JOIN sys.database_principals AS x
              ON z.grantee_principal_id = x.principal_id
      WHERE class = 3;        --class 3 = schema

  OPEN C_SQL;
  FETCH NEXT FROM C_SQL INTO @note, @id
  WHILE @@FETCH_STATUS = 0
      BEGIN
      IF LTRIM(RTRIM(@note)) <> ''''
          BEGIN
          PRINT @note;
          END;
      FETCH NEXT FROM C_SQL INTO @note, @id
      END;

  CLOSE C_SQL;
  DEALLOCATE C_SQL;';

  EXEC sp_MSforeachdb @command;

  EDIT: Fix it to actually be a runnable script.result.

  I am getting Msg 102, Level 15, State 1, Line 59
  Incorrect syntax near '>'.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 14, 2018 at 1:19 pm

  #1983279

  Runs fine on my machine.   Not sure where the trouble is...   Any chance of an accidental keystroke that messed with your copy after you pasted it into SSMS ?

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Nita Reddy

  SSCarpal Tunnel

  Points: 4177

  More actions

  March 14, 2018 at 1:28 pm

  #1983280

  sgmunson - Wednesday, March 14, 2018 12:48 PM

  Might want to try it this way:
  DECLARE @command AS varchar(2000) = '
  USE ?

  PRINT ''--=========================================================================='';
  PRINT ''USE ?'';
  PRINT ''--=========================================================================='';

  DECLARE @note AS varchar(2048), @id AS int;
  DECLARE C_sql CURSOR FOR
      SELECT
          CASE
              WHEN perm.state <> ''W'' THEN perm.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + perm.permission_name +
          SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default +        --TO <user name>
          CASE
              WHEN perm.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END
          AS [SQL ],
          1 AS status
      FROM sys.database_permissions AS perm
          INNER JOIN sys.database_principals AS usr
              ON perm.grantee_principal_id = usr.principal_id
      WHERE [perm].[major_id] = 0
          AND [usr].[principal_id] > 2
          AND [usr].[type] = ''U''
      UNION
      SELECT    '''', 2
      UNION
      SELECT '''', 3
      UNION
      SELECT
          CASE
              WHEN z.state <> ''W'' THEN z.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + z.permission_name +
          SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
          QUOTENAME(SCHEMA_NAME(major_id)) +
          SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
          CASE
              WHEN z.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END AS [test],
          4
      FROM sys.database_permissions AS z
          INNER JOIN sys.schemas AS s
              ON z.major_id = s.[schema_id]
          INNER JOIN sys.database_principals AS x
              ON z.grantee_principal_id = x.principal_id
      WHERE class = 3;        --class 3 = schema

  OPEN C_SQL;
  FETCH NEXT FROM C_SQL INTO @note, @id
  WHILE @@FETCH_STATUS = 0
      BEGIN
      IF LTRIM(RTRIM(@note)) <> ''''
          BEGIN
          PRINT @note;
          END;
      FETCH NEXT FROM C_SQL INTO @note, @id
      END;

  CLOSE C_SQL;
  DEALLOCATE C_SQL;';

  EXEC sp_MSforeachdb @command;

  EDIT: Fix it to actually be a runnable script.result.

  I re copied and still same error ( IF LTRIM(RTRIM(@note)) <> '''')

  Line 59
  🙁

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 14, 2018 at 1:52 pm

  #1983287

  Nita Reddy - Wednesday, March 14, 2018 1:28 PM

  sgmunson - Wednesday, March 14, 2018 12:48 PM

  Might want to try it this way:
  DECLARE @command AS varchar(2000) = '
  USE ?

  PRINT ''--=========================================================================='';
  PRINT ''USE ?'';
  PRINT ''--=========================================================================='';

  DECLARE @note AS varchar(2048), @id AS int;
  DECLARE C_sql CURSOR FOR
      SELECT
          CASE
              WHEN perm.state <> ''W'' THEN perm.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + perm.permission_name +
          SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default +        --TO <user name>
          CASE
              WHEN perm.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END
          AS [SQL ],
          1 AS status
      FROM sys.database_permissions AS perm
          INNER JOIN sys.database_principals AS usr
              ON perm.grantee_principal_id = usr.principal_id
      WHERE [perm].[major_id] = 0
          AND [usr].[principal_id] > 2
          AND [usr].[type] = ''U''
      UNION
      SELECT    '''', 2
      UNION
      SELECT '''', 3
      UNION
      SELECT
          CASE
              WHEN z.state <> ''W'' THEN z.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + z.permission_name +
          SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
          QUOTENAME(SCHEMA_NAME(major_id)) +
          SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
          CASE
              WHEN z.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END AS [test],
          4
      FROM sys.database_permissions AS z
          INNER JOIN sys.schemas AS s
              ON z.major_id = s.[schema_id]
          INNER JOIN sys.database_principals AS x
              ON z.grantee_principal_id = x.principal_id
      WHERE class = 3;        --class 3 = schema

  OPEN C_SQL;
  FETCH NEXT FROM C_SQL INTO @note, @id
  WHILE @@FETCH_STATUS = 0
      BEGIN
      IF LTRIM(RTRIM(@note)) <> ''''
          BEGIN
          PRINT @note;
          END;
      FETCH NEXT FROM C_SQL INTO @note, @id
      END;

  CLOSE C_SQL;
  DEALLOCATE C_SQL;';

  EXEC sp_MSforeachdb @command;

  EDIT: Fix it to actually be a runnable script.result.

  I re copied and still same error ( IF LTRIM(RTRIM(@note)) <> '''')

  Line 59
  🙁

  Change the @command variable declaration to varchar(4000).  The code is too long for 2000 if you paste it, because the tabs I put in get translated to spaces.   Also, be sure to take out all those spaces and replace with the intended tabs.   Once I did that, copy and paste from that post finally worked.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Nita Reddy

  SSCarpal Tunnel

  Points: 4177

  More actions

  March 14, 2018 at 2:54 pm

  #1983306

  sgmunson - Wednesday, March 14, 2018 1:52 PM

  Nita Reddy - Wednesday, March 14, 2018 1:28 PM

  sgmunson - Wednesday, March 14, 2018 12:48 PM

  Might want to try it this way:
  DECLARE @command AS varchar(2000) = '
  USE ?

  PRINT ''--=========================================================================='';
  PRINT ''USE ?'';
  PRINT ''--=========================================================================='';

  DECLARE @note AS varchar(2048), @id AS int;
  DECLARE C_sql CURSOR FOR
      SELECT
          CASE
              WHEN perm.state <> ''W'' THEN perm.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + perm.permission_name +
          SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default +        --TO <user name>
          CASE
              WHEN perm.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END
          AS [SQL ],
          1 AS status
      FROM sys.database_permissions AS perm
          INNER JOIN sys.database_principals AS usr
              ON perm.grantee_principal_id = usr.principal_id
      WHERE [perm].[major_id] = 0
          AND [usr].[principal_id] > 2
          AND [usr].[type] = ''U''
      UNION
      SELECT    '''', 2
      UNION
      SELECT '''', 3
      UNION
      SELECT
          CASE
              WHEN z.state <> ''W'' THEN z.state_desc
              ELSE ''GRANT''
          END +
          SPACE(1) + z.permission_name +
          SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
          QUOTENAME(SCHEMA_NAME(major_id)) +
          SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
          CASE
              WHEN z.state <> ''W'' THEN SPACE(0)
              ELSE SPACE(1) + ''WITH GRANT OPTION''
          END AS [test],
          4
      FROM sys.database_permissions AS z
          INNER JOIN sys.schemas AS s
              ON z.major_id = s.[schema_id]
          INNER JOIN sys.database_principals AS x
              ON z.grantee_principal_id = x.principal_id
      WHERE class = 3;        --class 3 = schema

  OPEN C_SQL;
  FETCH NEXT FROM C_SQL INTO @note, @id
  WHILE @@FETCH_STATUS = 0
      BEGIN
      IF LTRIM(RTRIM(@note)) <> ''''
          BEGIN
          PRINT @note;
          END;
      FETCH NEXT FROM C_SQL INTO @note, @id
      END;

  CLOSE C_SQL;
  DEALLOCATE C_SQL;';

  EXEC sp_MSforeachdb @command;

  EDIT: Fix it to actually be a runnable script.result.

  I re copied and still same error ( IF LTRIM(RTRIM(@note)) <> '''')

  Line 59
  🙁

  Change the @command variable declaration to varchar(4000).  The code is too long for 2000 if you paste it, because the tabs I put in get translated to spaces.   Also, be sure to take out all those spaces and replace with the intended tabs.   Once I did that, copy and paste from that post finally worked.

  Thanks for all your time

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 15, 2018 at 6:16 am

  #1983363

  So, it worked then?

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)