none
Cursor: T-SQL Befehl wird erzeugt und abgeschnitten

    Frage

  • Liebe SQL Freunde,

    Sinn der Sache, immer eine Liste aller Veränderungen aller DB Benurtzer parat zu haben.

    Die letzte Zeile folgender Befehls wird abgeschnitten. Was mache ich falsch? 

    declare @cDBName nvarchar(255)
    declare @sql as nvarchar(max)
    set @sql=''
    declare c cursor for 
    select name from master..sysdatabases a
    where a.dbid not in (1,2,3,4)
    open c; 
    fetch next from c into @cDBName;
    WHILE @@FETCH_STATUS = 0 
    begin
    
    --
    set @sql =  '    
    INSERT INTO [dbo].[DatabaseSecurity]
    
                           ([ServerName]
                                 ,[DBName]
                                 ,[SRVLoginName]
                                 ,[SRVPrincipalID]
                                 ,[SRVRoleName]
                                 ,[MappedAsUser]
                                 ,[DBDefaultSchema]
                                 ,[DBUserCreateDate]
                                 ,[DBUSerModifyDate]
                                 ,[DB_is_fixed_role_login]
                                 ,[DBRoleName]
                                 ,[DBRoleType]
                                 ,[DBRoleType_Desc]
                                 ,[DBRoleCreateDate]
                                 ,[DBRoleModifyDate]
                                 ,[DB_is_fixed_role]
                                 ,[OwnedSchemaNames]
                                 ,[TimeStamp]
                                 ,[DateKey]
                                 ,[SRVLoginType_Desc]
                                 ,[SRVRoleIs_Disabled])
    							 
    							     
                 SELECT @@Servername COLLATE DATABASE_DEFAULT AS ServerName
                 , '''+@cDBName+''' COLLATE DATABASE_DEFAULT AS DBName
                 ,SRVLogin.name COLLATE DATABASE_DEFAULT AS SRVLoginName
                 ,SRVLogin.principal_id AS SRVPrincipalID
                 ,COALESCE(SRVRole.name, ''No Server Role'') COLLATE DATABASE_DEFAULT AS SRVRoleName
                 ,DBLogin.name COLLATE DATABASE_DEFAULT AS MappedAsUser
                 ,DBLogin.default_schema_name COLLATE DATABASE_DEFAULT AS DBDefaultSchema
                 ,DBLogin.create_date AS DBUserCreateDate
                 ,DBLogin.modify_date AS DBUSerModifyDate
                 ,DBLogin.is_fixed_role AS DB_is_fixed_role_login
                 ,DBRoleLogins.name COLLATE DATABASE_DEFAULT AS DBRoleName
                 ,DBRoleLogins.type COLLATE DATABASE_DEFAULT AS DBRoleType
                 ,DBRoleLogins.type_desc COLLATE DATABASE_DEFAULT AS DBRoleType_Desc
                 ,DBRoleLogins.create_date AS DBRoleCreateDate
                 ,DBRoleLogins.modify_date AS DBRoleModifyDate
                 ,DBRoleLogins.is_fixed_role AS DB_is_fixed_role
                 ,DBSchemas.name COLLATE DATABASE_DEFAULT AS OwnedSchemaNames
                 ,GETDATE() AS TimeStamp
                 ,convert(int,convert(varchar(35), getdate(),112)) AS DateKey
                 ,SRVLogin.type_desc COLLATE DATABASE_DEFAULT AS SRVLoginType_Desc
                 ,SRVRole.is_disabled AS SRVRoleIs_Disabled 
    			         
                 FROM ['+@cDBName+'].sys.database_principals AS DBLogin WITH (NOLOCK) 
    			 LEFT JOIN ['+@cDBName+'].sys.database_role_members AS DBRoles WITH (NOLOCK) ON DBLogin.principal_id = DBRoles.member_principal_id 
    			 LEFT JOIN ['+@cDBName+'].sys.database_principals AS DBRoleLogins WITH (NOLOCK) ON DBRoles.role_principal_id = DBRoleLogins.principal_id 
    			 LEFT JOIN ['+@cDBName+'].sys.schemas AS DBSchemas WITH (NOLOCK) ON DBLogin.principal_id = DBSchemas.principal_id 
    			 LEFT JOIN master.sys.server_principals AS SRVLogin WITH (NOLOCK) ON SRVLogin.sid = DBLogin.sid 
    			 LEFT JOIN master.sys.server_role_members AS SRM WITH (NOLOCK) ON SRVLogin.principal_ID = SRM.member_principal_id 
    			 LEFT JOIN master.sys.server_principals AS SRVRole WITH (NOLOCK) ON SRM.role_principal_id = SRVRole.principal_ID 
    			 LEFT JOIN master.sys.credentials AS SRVCredential WITH (NOLOCK) ON SRVLogin.credential_id = SRVCredential.credential_id 
    			 WHERE SRVLogin.type IN (''S'', ''G'', ''U'')
    			 except
    			 select [ServerName], [DBName], [SRVLoginName], [SRVPrincipalID], [SRVRoleName], [MappedAsUser], [DBDefaultSchema], [DBUserCreateDate], [DBUSerModifyDate], [DB_is_fixed_role_login], [DBRoleName], [DBRoleType], [DBRoleType_Desc], [DBRoleCreateDate], [DBRoleModifyDate], [DB_is_fixed_role], [OwnedSchemaNames]
    			 ,GETDATE() AS [TimeStamp]
    			 ,convert(int,convert(varchar(35), getdate(),112)) AS DateKey
    			 , [SRVLoginType_Desc], [SRVRoleIs_Disabled]
    			 from [dbo].[DataBaseSecurity]
    
    '
    print (@sql)
    
    fetch next from c into @cDBName;
    set @sql += @sql 
    
    
    end;
    CLOSE c;
    DEALLOCATE c;
    
    
    

    Danke & Gruß

    Irina


    Irina

    Dienstag, 6. März 2018 11:01

Antworten

  • Hallo Irina,

    nein, der exec Befehl hat kein Limit von 8.000 Zeichen! Da ist vermutlich etwas anderes falsch. Deklariere deine Variablen mal nicht als Unicode, also so:

    declare @cDBName varchar(255)
    declare
    @sql as varchar(max)

    ... das sollte für deinen Fall ausreichend sein.

    Liebe Grüße Volker

    • Als Antwort markiert soarian2 Dienstag, 6. März 2018 13:31
    Dienstag, 6. März 2018 11:46
  • Hallo Irina,

    ich habe das mit deinem Code 'mal ausprobiert - das "Except" funktioniert bei mir einwandfrei!

    DECLARE @cDBName VARCHAR(255);
    DECLARE @sql AS VARCHAR(MAX)= ''
    
    DECLARE c CURSOR FOR
    SELECT name FROM master..sysdatabases a WHERE a.dbid NOT IN (1, 2, 3, 4);
    
    OPEN c;
    
    FETCH NEXT FROM c
    INTO @cDBName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        --
        SET @sql = ' 
    INSERT INTO dbo.DatabaseSecurity
    
    (ServerName,
     DBName,
     SRVLoginName,
     SRVPrincipalID,
     SRVRoleName,
     MappedAsUser,
     DBDefaultSchema,
     DBUserCreateDate,
     DBUSerModifyDate,
     DB_is_fixed_role_login,
     DBRoleName,
     DBRoleType,
     DBRoleType_Desc,
     DBRoleCreateDate,
     DBRoleModifyDate,
     DB_is_fixed_role,
     OwnedSchemaNames,
     TimeStamp,
     DateKey,
     SRVLoginType_Desc,
     SRVRoleIs_Disabled)
     	   
    SELECT    @@SERVERNAME COLLATE DATABASE_DEFAULT                              AS ServerName,
              ''' + @cDBName + ''' COLLATE DATABASE_DEFAULT                      AS DBName,
              SRVLogin.name COLLATE DATABASE_DEFAULT                             AS SRVLoginName,
              SRVLogin.principal_id                                              AS SRVPrincipalID,
              coalesce(SRVRole.name, ''No Server Role'')COLLATE DATABASE_DEFAULT AS SRVRoleName,
              DBLogin.name COLLATE DATABASE_DEFAULT                              AS MappedAsUser,
              DBLogin.default_schema_name COLLATE DATABASE_DEFAULT               AS DBDefaultSchema,
              DBLogin.create_date                                                AS DBUserCreateDate,
              DBLogin.modify_date                                                AS DBUSerModifyDate,
              DBLogin.is_fixed_role                                              AS DB_is_fixed_role_login,
              DBRoleLogins.name COLLATE DATABASE_DEFAULT                         AS DBRoleName,
              DBRoleLogins.type COLLATE DATABASE_DEFAULT                         AS DBRoleType,
              DBRoleLogins.type_desc COLLATE DATABASE_DEFAULT                    AS DBRoleType_Desc,
              DBRoleLogins.create_date                                           AS DBRoleCreateDate,
              DBRoleLogins.modify_date                                           AS DBRoleModifyDate,
              DBRoleLogins.is_fixed_role                                         AS DB_is_fixed_role,
              DBSchemas.name COLLATE DATABASE_DEFAULT                            AS OwnedSchemaNames,
              getDate()                                                          AS TimeStamp,
              convert(INT, convert(VARCHAR(35), getDate(), 112))                 AS DateKey,
              SRVLogin.type_desc COLLATE DATABASE_DEFAULT                        AS SRVLoginType_Desc,
              SRVRole.is_disabled                                                AS SRVRoleIs_Disabled
    
    FROM
              [' + @cDBName + '].sys.database_principals   AS DBLogin WITH (NOLOCK)
    LEFT JOIN [' + @cDBName + '].sys.database_role_members AS DBRoles WITH (NOLOCK) ON DBLogin.principal_id           = DBRoles.member_principal_id
    LEFT JOIN [' + @cDBName + '].sys.database_principals   AS DBRoleLogins WITH (NOLOCK) ON DBRoles.role_principal_id = DBRoleLogins.principal_id
    LEFT JOIN [' + @cDBName
                   + '].sys.schemas               AS DBSchemas WITH (NOLOCK) ON DBLogin.principal_id         = DBSchemas.principal_id
    LEFT JOIN master.sys.server_principals               AS SRVLogin WITH (NOLOCK) ON SRVLogin.sid                  = DBLogin.sid
    LEFT JOIN master.sys.server_role_members             AS SRM WITH (NOLOCK) ON SRVLogin.principal_id              = SRM.member_principal_id
    LEFT JOIN master.sys.server_principals               AS SRVRole WITH (NOLOCK) ON SRM.role_principal_id          = SRVRole.principal_id
    LEFT JOIN master.sys.credentials                     AS SRVCredential WITH (NOLOCK) ON SRVLogin.credential_id   = SRVCredential.credential_id
    WHERE
              SRVLogin.type IN (''S'', ''G'', ''U'')
    EXCEPT
    SELECT ServerName,
           DBName,
           SRVLoginName,
           SRVPrincipalID,
           SRVRoleName,
           MappedAsUser,
           DBDefaultSchema,
           DBUserCreateDate,
           DBUSerModifyDate,
           DB_is_fixed_role_login,
           DBRoleName,
           DBRoleType,
           DBRoleType_Desc,
           DBRoleCreateDate,
           DBRoleModifyDate,
           DB_is_fixed_role,
           OwnedSchemaNames,
           getDate()                                          AS TimeStamp,
           convert(INT, convert(VARCHAR(35), getDate(), 112)) AS DateKey,
           SRVLoginType_Desc,
           SRVRoleIs_Disabled
    FROM   dbo.DataBaseSecurity;
    '   ;
    
        EXEC (@sql);
    
        FETCH NEXT FROM c
        INTO @cDBName;
    
    END;
    
    CLOSE c;
    DEALLOCATE c;


    Dienstag, 6. März 2018 13:13
  • Danke sehr! Mit dem except habe ich verstanden. In meinem Statement stört eindeutig Feld "Timestamp".

    Aber warum der Nvarchar Datentyp stört, verstehe ich leider nicht.


    Irina

    • Als Antwort markiert soarian2 Dienstag, 6. März 2018 13:44
    Dienstag, 6. März 2018 13:31

Alle Antworten

  • Hallo Irina,

    der Print Befehl kann nicht mit so vielen Zeichen umgehen. Dieser ist auf 8.000 Zeichen (4.000 bei Unicode) begrenzt!

    Siehe MS DOCS

    Grüße Volker

    Dienstag, 6. März 2018 11:24
  • Danke Volker,

    offensichtlich hat der exec(@sql) Befehl denselben Limit an Zeihen? Was kan ich tun?

    (13 rows affected)
    
    (11 rows affected)
    Msg 105, Level 15, State 1, Line 65
    Unclosed quotation mark after the character string 'DataBaseSecurit'.
    Msg 102, Level 15, State 1, Line 65
    Incorrect syntax near 'DataBaseSecurit'.
    Msg 105, Level 15, State 1, Line 65
    Unclosed quotation mark after the character string 'DataB'.
    Msg 102, Level 15, State 1, Line 65
    Incorrect syntax near 'DataB'.
    
    (14 rows affected)
    Msg 105, Level 15, State 1, Line 65
    Unclosed quotation mark after the character string 'DataBaseSecurit'.
    Msg 102, Level 15, State 1, Line 65
    Incorrect syntax near 'DataBaseSecurit'.
    Msg 1038, Level 15, State 4, Line 65
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
    Msg 105, Level 15, State 1, Line 65
    Unclosed quotation mark after the character string ''.
    Msg 102, Level 15, State 1, Line 65
    Incorrect syntax near ''.
    
    (1 row affected)


    Irina

    Dienstag, 6. März 2018 11:37
  • Auch execute "sp_executesql @sql" gibt denselben Fehler aus...


    Irina

    Dienstag, 6. März 2018 11:41
  • Hallo Irina,

    nein, der exec Befehl hat kein Limit von 8.000 Zeichen! Da ist vermutlich etwas anderes falsch. Deklariere deine Variablen mal nicht als Unicode, also so:

    declare @cDBName varchar(255)
    declare
    @sql as varchar(max)

    ... das sollte für deinen Fall ausreichend sein.

    Liebe Grüße Volker

    • Als Antwort markiert soarian2 Dienstag, 6. März 2018 13:31
    Dienstag, 6. März 2018 11:46
  • Das ist erstaunlich, aber als varchar(max) funktioniert es. Ich kann allerdings nicht begreifen, warum...

    Allerdings hat das "Except" keine Wirkung. Es sollen entweder veränderte oder neue Zeilen eingefühgt werden. So dass man nachverfolgen kann, ob ein User irgendwie verendert wurde oder ein neue User hinzugefügt.

    Hättest Du hierzu eine Idee?


    Irina

    • Als Antwort markiert soarian2 Dienstag, 6. März 2018 13:31
    • Tag als Antwort aufgehoben soarian2 Dienstag, 6. März 2018 13:31
    Dienstag, 6. März 2018 12:00
  • Hallo Irina,

    ich habe das mit deinem Code 'mal ausprobiert - das "Except" funktioniert bei mir einwandfrei!

    DECLARE @cDBName VARCHAR(255);
    DECLARE @sql AS VARCHAR(MAX)= ''
    
    DECLARE c CURSOR FOR
    SELECT name FROM master..sysdatabases a WHERE a.dbid NOT IN (1, 2, 3, 4);
    
    OPEN c;
    
    FETCH NEXT FROM c
    INTO @cDBName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        --
        SET @sql = ' 
    INSERT INTO dbo.DatabaseSecurity
    
    (ServerName,
     DBName,
     SRVLoginName,
     SRVPrincipalID,
     SRVRoleName,
     MappedAsUser,
     DBDefaultSchema,
     DBUserCreateDate,
     DBUSerModifyDate,
     DB_is_fixed_role_login,
     DBRoleName,
     DBRoleType,
     DBRoleType_Desc,
     DBRoleCreateDate,
     DBRoleModifyDate,
     DB_is_fixed_role,
     OwnedSchemaNames,
     TimeStamp,
     DateKey,
     SRVLoginType_Desc,
     SRVRoleIs_Disabled)
     	   
    SELECT    @@SERVERNAME COLLATE DATABASE_DEFAULT                              AS ServerName,
              ''' + @cDBName + ''' COLLATE DATABASE_DEFAULT                      AS DBName,
              SRVLogin.name COLLATE DATABASE_DEFAULT                             AS SRVLoginName,
              SRVLogin.principal_id                                              AS SRVPrincipalID,
              coalesce(SRVRole.name, ''No Server Role'')COLLATE DATABASE_DEFAULT AS SRVRoleName,
              DBLogin.name COLLATE DATABASE_DEFAULT                              AS MappedAsUser,
              DBLogin.default_schema_name COLLATE DATABASE_DEFAULT               AS DBDefaultSchema,
              DBLogin.create_date                                                AS DBUserCreateDate,
              DBLogin.modify_date                                                AS DBUSerModifyDate,
              DBLogin.is_fixed_role                                              AS DB_is_fixed_role_login,
              DBRoleLogins.name COLLATE DATABASE_DEFAULT                         AS DBRoleName,
              DBRoleLogins.type COLLATE DATABASE_DEFAULT                         AS DBRoleType,
              DBRoleLogins.type_desc COLLATE DATABASE_DEFAULT                    AS DBRoleType_Desc,
              DBRoleLogins.create_date                                           AS DBRoleCreateDate,
              DBRoleLogins.modify_date                                           AS DBRoleModifyDate,
              DBRoleLogins.is_fixed_role                                         AS DB_is_fixed_role,
              DBSchemas.name COLLATE DATABASE_DEFAULT                            AS OwnedSchemaNames,
              getDate()                                                          AS TimeStamp,
              convert(INT, convert(VARCHAR(35), getDate(), 112))                 AS DateKey,
              SRVLogin.type_desc COLLATE DATABASE_DEFAULT                        AS SRVLoginType_Desc,
              SRVRole.is_disabled                                                AS SRVRoleIs_Disabled
    
    FROM
              [' + @cDBName + '].sys.database_principals   AS DBLogin WITH (NOLOCK)
    LEFT JOIN [' + @cDBName + '].sys.database_role_members AS DBRoles WITH (NOLOCK) ON DBLogin.principal_id           = DBRoles.member_principal_id
    LEFT JOIN [' + @cDBName + '].sys.database_principals   AS DBRoleLogins WITH (NOLOCK) ON DBRoles.role_principal_id = DBRoleLogins.principal_id
    LEFT JOIN [' + @cDBName
                   + '].sys.schemas               AS DBSchemas WITH (NOLOCK) ON DBLogin.principal_id         = DBSchemas.principal_id
    LEFT JOIN master.sys.server_principals               AS SRVLogin WITH (NOLOCK) ON SRVLogin.sid                  = DBLogin.sid
    LEFT JOIN master.sys.server_role_members             AS SRM WITH (NOLOCK) ON SRVLogin.principal_id              = SRM.member_principal_id
    LEFT JOIN master.sys.server_principals               AS SRVRole WITH (NOLOCK) ON SRM.role_principal_id          = SRVRole.principal_id
    LEFT JOIN master.sys.credentials                     AS SRVCredential WITH (NOLOCK) ON SRVLogin.credential_id   = SRVCredential.credential_id
    WHERE
              SRVLogin.type IN (''S'', ''G'', ''U'')
    EXCEPT
    SELECT ServerName,
           DBName,
           SRVLoginName,
           SRVPrincipalID,
           SRVRoleName,
           MappedAsUser,
           DBDefaultSchema,
           DBUserCreateDate,
           DBUSerModifyDate,
           DB_is_fixed_role_login,
           DBRoleName,
           DBRoleType,
           DBRoleType_Desc,
           DBRoleCreateDate,
           DBRoleModifyDate,
           DB_is_fixed_role,
           OwnedSchemaNames,
           getDate()                                          AS TimeStamp,
           convert(INT, convert(VARCHAR(35), getDate(), 112)) AS DateKey,
           SRVLoginType_Desc,
           SRVRoleIs_Disabled
    FROM   dbo.DataBaseSecurity;
    '   ;
    
        EXEC (@sql);
    
        FETCH NEXT FROM c
        INTO @cDBName;
    
    END;
    
    CLOSE c;
    DEALLOCATE c;


    Dienstag, 6. März 2018 13:13
  • Danke sehr! Mit dem except habe ich verstanden. In meinem Statement stört eindeutig Feld "Timestamp".

    Aber warum der Nvarchar Datentyp stört, verstehe ich leider nicht.


    Irina

    • Als Antwort markiert soarian2 Dienstag, 6. März 2018 13:44
    Dienstag, 6. März 2018 13:31
  • Genau gesagt handelt es sich um die Variable @cDBName. @sql kann als Nvarchar(max) deklariert werden.

    Dh. dass in eine Nvarchar Zeichenkette knn man kein string variabler Länge einfügen. Ist das ungefähr so zu verstehen?


    Irina

    Dienstag, 6. März 2018 13:40
  • Hallo Irina,

    der nvarchar Datentyp "stört" hier an sich nicht - es ist so: Dein dynamisches SQL ist an die 4.000 Zeichen lang (je nach Inhalt deiner @cDBName - Variable) - nvarchar ist Unicode und benötigt je Zeichen 2 Bytes. Daher kann der print Befehl 4000 Zeichen bei nvarchar und 8000 Zeichen bei varchar verarbeiten.

    Du hättest auch durch das Eliminieren "unnötiger" Zeichen wie z.B. der [ / ] - Zeichen dein SQL evtl. so verkleinern können, dass das ganze auch mit nvarchar funktioniert hätte.

    Grüße Volker

    Mittwoch, 7. März 2018 15:54