Identifying list of users or groups that have create table permission

  • Experts,

    Do you'll know of a way to get details on how to get the list of users/groups (we have lot of group accounts) that have create table permission or is part of db_ddlamin group or db_owner or sysadmin etc. I would like to get these details all part of a single script that can loop through all the DBs and get me the culprits. Since, I have more than 25 odd databases getting to each one by one would be a pain. It would be really helpful if there is a way to get around it so I can have everything listed in one shot.

    Thanks

  • Take a look at the free scripts of Kenneth Fisher (https://sqlstudies.com/free-scripts/). He has a script to list all database permissions. You can adjust the script by yourself to get only the output to your need.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • HanShi - Monday, April 30, 2018 5:47 AM

    Take a look at the free scripts of Kenneth Fisher (https://sqlstudies.com/free-scripts/). He has a script to list all database permissions. You can adjust the script by yourself to get only the output to your need.

    Can you provide me the exact script link because I could not locate that script.

  • Feivel - Monday, April 30, 2018 11:12 AM

    HanShi - Monday, April 30, 2018 5:47 AM

    Take a look at the free scripts of Kenneth Fisher (https://sqlstudies.com/free-scripts/). He has a script to list all database permissions. You can adjust the script by yourself to get only the output to your need.

    Can you provide me the exact script link because I could not locate that script.

    Try this link for his script:
    sp_DBPermissions

    Sue

  • Sue_H - Monday, April 30, 2018 11:25 AM

    Feivel - Monday, April 30, 2018 11:12 AM

    HanShi - Monday, April 30, 2018 5:47 AM

    Try this link for his script:
    sp_DBPermissions

    Sue

    Thanks for providing the direct link Sue. I wasn't that fast putting a reply together on my mobile.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Simply querying system tables won't cut it. Check out the sys.fn_my_permissions function which can be used to determine "effective" permissions, meaning it covers permissions inherited by membership in domain, server, or database role.
    https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-my-permissions-transact-sql?view=sql-server-2017

    Also, near bottom of article note the use of "EXECUTE AS LOGIN" to impersonate an account, which is essential for this.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • HanShi - Monday, April 30, 2018 11:45 AM

    Sue_H - Monday, April 30, 2018 11:25 AM

    Feivel - Monday, April 30, 2018 11:12 AM

    HanShi - Monday, April 30, 2018 5:47 AM

    Try this link for his script:
    sp_DBPermissions

    Sue

    Thanks for providing the direct link Sue. I wasn't that fast putting a reply together on my mobile.

    It was odd - I had it opened in another tab from your link when I saw the question.
    Nice bunch of scripts in that link so thanks for posting that. Never ran across it before.

    Sue

  • I think I am doing something wrong here. Basically, it is looping through all the DBs but is actually not outputting the entire result set. I am not sure where I am going wrong. Also, I wanted to append the DB name in the column list so that I know where it is coming from.

    DECLARE @db_name AS NVARCHAR(MAX);
    DECLARE @sql AS NVARCHAR(MAX);

    DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name
    FROM master.sys.databases
    WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
           'ReportServerTempDB'
          )
      AND name NOT LIKE '%CDW%'
      AND name NOT LIKE '%demo%'; -- exclude these databases

    CREATE TABLE #DBRoles
    (
      Principal VARCHAR(100),
      DBRole VARCHAR(100)
    );

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor
    INTO @db_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN

      SET @sql = 'USE [' + @db_name + ']';
      PRINT (@sql);
      EXEC (@sql);

      INSERT INTO #DBRoles
      (
       Principal,
       DBRole
      )
      SELECT P1.name AS Principal,
        P2.name AS DBRole
      FROM sys.database_principals AS P1
       INNER JOIN sys.database_role_members AS RM
        ON RM.member_principal_id = P1.principal_id
       INNER JOIN sys.database_principals AS P2
        ON P2.principal_id = RM.role_principal_id
      WHERE P2.name IN ( 'db_owner', 'db_ddladmin' );

      FETCH NEXT FROM db_cursor
      INTO @db_name;
    END;

    SELECT * FROM #DBRoles;
    DROP TABLE #DBRoles;

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

  • Feivel - Monday, April 30, 2018 12:19 PM

    I think I am doing something wrong here. Basically, it is looping through all the DBs but is actually not outputting the entire result set. I am not sure where I am going wrong. Also, I wanted to append the DB name in the column list so that I know where it is coming from.

    DECLARE @db_name AS NVARCHAR(MAX);
    DECLARE @sql AS NVARCHAR(MAX);

    DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name
    FROM master.sys.databases
    WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
           'ReportServerTempDB'
          )
      AND name NOT LIKE '%CDW%'
      AND name NOT LIKE '%demo%'; -- exclude these databases

    CREATE TABLE #DBRoles
    (
      Principal VARCHAR(100),
      DBRole VARCHAR(100)
    );

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor
    INTO @db_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN

      SET @sql = 'USE [' + @db_name + ']';
      PRINT (@sql);
      EXEC (@sql);

      INSERT INTO #DBRoles
      (
       Principal,
       DBRole
      )
      SELECT P1.name AS Principal,
        P2.name AS DBRole
      FROM sys.database_principals AS P1
       INNER JOIN sys.database_role_members AS RM
        ON RM.member_principal_id = P1.principal_id
       INNER JOIN sys.database_principals AS P2
        ON P2.principal_id = RM.role_principal_id
      WHERE P2.name IN ( 'db_owner', 'db_ddladmin' );

      FETCH NEXT FROM db_cursor
      INTO @db_name;
    END;

    SELECT * FROM #DBRoles;
    DROP TABLE #DBRoles;

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    sp_executesql (and other forms of dynamic SQL) aren't run in exactly the same context as your session.  You are doing the USE inside the dynamic SQL script, but as soon as you exit that script you are back in your current database.  So you are only ever hitting the current database, over and over again.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Kenneth.Fisher - Monday, April 30, 2018 3:03 PM

    Feivel - Monday, April 30, 2018 12:19 PM

    I think I am doing something wrong here. Basically, it is looping through all the DBs but is actually not outputting the entire result set. I am not sure where I am going wrong. Also, I wanted to append the DB name in the column list so that I know where it is coming from.

    DECLARE @db_name AS NVARCHAR(MAX);
    DECLARE @sql AS NVARCHAR(MAX);

    DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name
    FROM master.sys.databases
    WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
           'ReportServerTempDB'
          )
      AND name NOT LIKE '%CDW%'
      AND name NOT LIKE '%demo%'; -- exclude these databases

    CREATE TABLE #DBRoles
    (
      Principal VARCHAR(100),
      DBRole VARCHAR(100)
    );

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor
    INTO @db_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN

      SET @sql = 'USE [' + @db_name + ']';
      PRINT (@sql);
      EXEC (@sql);

      INSERT INTO #DBRoles
      (
       Principal,
       DBRole
      )
      SELECT P1.name AS Principal,
        P2.name AS DBRole
      FROM sys.database_principals AS P1
       INNER JOIN sys.database_role_members AS RM
        ON RM.member_principal_id = P1.principal_id
       INNER JOIN sys.database_principals AS P2
        ON P2.principal_id = RM.role_principal_id
      WHERE P2.name IN ( 'db_owner', 'db_ddladmin' );

      FETCH NEXT FROM db_cursor
      INTO @db_name;
    END;

    SELECT * FROM #DBRoles;
    DROP TABLE #DBRoles;

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    sp_executesql (and other forms of dynamic SQL) aren't run in exactly the same context as your session.  You are doing the USE inside the dynamic SQL script, but as soon as you exit that script you are back in your current database.  So you are only ever hitting the current database, over and over again.

    Thanks! I fixed the script but I am not sure how do I input the DB name column.

  • Feivel - Monday, April 30, 2018 3:38 PM

    Kenneth.Fisher - Monday, April 30, 2018 3:03 PM

    sp_executesql (and other forms of dynamic SQL) aren't run in exactly the same context as your session.  You are doing the USE inside the dynamic SQL script, but as soon as you exit that script you are back in your current database.  So you are only ever hitting the current database, over and over again.

    Thanks! I fixed the script but I am not sure how do I input the DB name column.

    There are two options to get the name of the database in the result. You can use the value of the variable @db_name, which you use to loop through the databases. Personally I prefer to use the function DB_NAME() to return the current/active database name.
    To implement this in your script you need to add a column to the temp table and additionally use one of the obove methots to expand the SELECT. The modified script will look something like this:

    DECLARE @db_name AS NVARCHAR(MAX);
    DECLARE @sql AS NVARCHAR(MAX);

    DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name
    FROM master.sys.databases
    WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
       'ReportServerTempDB'
      )
    AND name NOT LIKE '%CDW%'
    AND name NOT LIKE '%demo%'; -- exclude these databases

    CREATE TABLE #DBRoles
    (
    DBName VARCHAR(100),
    Principal VARCHAR(100),
    DBRole VARCHAR(100)
    );

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor
    INTO @db_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @sql = 'USE [' + @db_name + ']
      INSERT INTO #DBRoles
    (
      DBName,
        Principal,
      DBRole
    )
    SELECT DB_NAME() as DBName,
      P1.name AS Principal,
      P2.name AS DBRole
    FROM sys.database_principals AS P1
      INNER JOIN sys.database_role_members AS RM
      ON RM.member_principal_id = P1.principal_id
      INNER JOIN sys.database_principals AS P2
      ON P2.principal_id = RM.role_principal_id
    WHERE P2.name IN ( ''db_owner'', ''db_ddladmin'' );'

    PRINT (@sql);
    EXEC (@sql);

    FETCH NEXT FROM db_cursor
    INTO @db_name;
    END;

    SELECT * FROM #DBRoles;
    DROP TABLE #DBRoles;

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 11 posts - 1 through 11 (of 11 total)

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