Post reply

Strange error with sp_msforeachdb

  • August 16, 2012 at 7:56 am

    #262950

    So I'm trying to create a security dictionary - single table, containing one row per server login/database/database user/granted permission combination.

    Not exactly a well-normalised design but anyway...

    My code is below. I'm getting a strange error back ... and I can't spot what might have gone wrong. It reads like a stray ? somewhere. Has anyone encountered it before?

    SET NOCOUNT ON

    --CREATE TABLE SANDBOX_DEV.dbo.NewMapLoginsToUsersToPermissions (

    --database_name VARCHAR(100),date_queried VARCHAR(11),

    --login_name VARCHAR(100),username VARCHAR(100),

    --permission_name VARCHAR(100),state_desc VARCHAR(100) )

    EXEC sp_msforeachdb '

    INSERT INTO SANDBOX_DEV.dbo.NewMapLoginsToUsersToPermissions

    SELECT''?'' [database_name],

    CAST(LEFT(CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),11) AS VARCHAR(11)) [date_queried],

    sprin.name [login_name],

    dprin.name [username], sperm.permission_name, sperm.state_desc

    FROM sys.server_principals sprin

    LEFT JOIN sys.database_principals dprin ON sprin.sid = dprin.sid

    LEFT JOIN sys.database_permissions sperm ON dprin.principal_id = sperm.grantee_principal_id

    WHERE dprin.is_fixed_role = 0 AND dprin.name NOT IN (''public''','dbo'',''guest'',''sys'', ''INFORMATION_SCHEMA'')

    ORDER BY database_name ASC, login_name ASC, username ASC, permission_name ASC

    '

    SELECT * FROM SANDBOX_DEV.dbo.NewMapLoginsToUsersToPermissions

    Msg 195, Level 15, State 10, Line 5

    'GETmasterATE' is not a recognized built-in function name.

    Msg 55555, Level 16, State 1, Procedure sp_MSforeach_worker, Line 92

    sp_MSforeach_worker assert failed: command too long

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • August 16, 2012 at 8:53 am

    #1526645

    Got it. Your list of database names isn't quite right...

    you've got

    AND dprin.name NOT IN (''public''','dbo'',''guest'',''sys'', ''INFORMATION_SCHEMA'')

    where you should have

    AND dprin.name NOT IN (''public'',''dbo'',''guest'',''sys'', ''INFORMATION_SCHEMA'')

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • September 6, 2012 at 7:47 am

    #1534465

    Brilliant, thanks - didn't spot that typo there!

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy