T-SQL and BCP to qureyout to a file on database premissions

  • Ok I at an end, I'm close but no grasping at straws. In this set of statement I collect instance and database permissions, put them into temp files, the merge the two files into one select. I'm now trying to dump that select into an output file. The command shell is opened and then closed, there are print statements that will show you results at each state, and there are also two different attempts to dispatch the query to a file. Can some please look and tell me why when the T-SQL is executed I get the bcp help menu back.

    -- OPEN the command SHELL

    EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'xp_cmdshell', 1

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'show advanced options', 0

    GO

    RECONFIGURE

    GO

    --Declare Variable need

    DECLARE @OutputFile NVARCHAR(100);

    DECLARE @FilePath NVARCHAR(100);

    DECLARE @bcpCommand NVARCHAR(1000);

    --Build Temp tables

    CREATE TABLE #InstanceLevel(

    in_name nvarchar(128) not null,

    in_type_desc nvarchar(60),

    is_disabled int,

    create_date datetime,

    modify_date datetime,

    Default_database_name nvarchar(128),

    sysadmin int,

    securityadmin int,

    serveradmin int,

    setupadmin int,

    processadmin int,

    diskadmin int,

    dbcreator int,

    bulkadmin int

    );

    -- Table for database level premissions

    CREATE TABLE #DBPremissions(

    DBName nvarchar(128) NOT NULL,

    MemberName nvarchar(128) NOT NULL,

    RoleName nvarchar(128) NOT NULL,

    DefaultSchema nvarchar (128),

    ServerLogin nvarchar(128) NOT NULL

    );

    -- Populate temp tables

    INSERT INTO [#InstanceLevel]

    SELECT sp.name

    ,sp.type_desc

    ,is_disabled

    ,sp.create_date

    ,sp.modify_date

    ,sp.Default_database_name

    ,sl.sysadmin

    ,sl.securityadmin

    ,sl.serveradmin

    ,sl.setupadmin

    ,sl.processadmin

    ,sl.diskadmin

    ,sl.dbcreator

    ,sl.bulkadmin

    FROM sys.server_principals sp

    JOIN sys.syslogins sl ON

    sp.sid = sl.sid

    where sp.type in ('S','G','U');

    --populate database premissions table

    INSERT INTO [#DBPremissions]

    EXEC sp_MSforeachdb 'use ?

    SELECT DB_NAME() as [Database name]

    ,MEM.name AS MemberName

    ,RL.name AS RoleName

    ,MEM.default_schema_name AS DefaultSchema

    ,SP.name AS ServerLogin

    FROM sys.database_role_members AS DRM

    INNER JOIN sys.database_principals AS RL

    ON DRM.role_principal_id = RL.principal_id

    INNER JOIN sys.database_principals AS MEM

    ON DRM.member_principal_id = MEM.principal_id

    INNER JOIN sys.server_principals AS SP

    ON MEM.[sid] = SP.[sid]';

    --- Test the REsults of the Insert

    /*

    SELECT *

    FROM #InstanceLevel

    order by in_name;

    SELECT *

    FROM #DBPremissions

    --where MemberName = 'A70ADOM\LS_WMSOPS'

    order by MemberName;

    SELECT il.*

    ,dbp.*

    FROM #InstanceLevel as il

    JOIN #DBPremissions as dbp

    ON il.in_name = dbp.MemberName

    --WHERE dbp.MemberName ='A70ADOM\LS_WMSOPS'

    order by il.in_name;

    */

    --Try1

    /*

    SET @bcpCommand = 'bcp "SELECT il.*

    ,dbp.*

    FROM #InstanceLevel as il

    JOIN #DBPremissions as dbp

    ON il.in_name = dbp.MemberName

    order by il.in_name" queryout D:\test_it.txt -c -T -t'

    */

    --Try2

    SET @bcpCommand = 'bcp "SELECT il.In_name

    ,il.in_type_desc

    ,il.is_disabled

    ,il.create_date

    ,il.modify_date

    ,il.Default_database_name

    ,il.sysadmin

    ,il.securityadmin

    ,il.serveradmin

    ,il.setupadmin

    ,il.processadmin

    ,il.diskadmin

    ,il.dbcreator

    ,il.bulkadmin

    ,dbp.DBName

    ,dbp.MemberName

    ,dbp.RoleName

    ,dbp.DefaultSchema

    ,dbp.ServerLogin

    FROM #InstanceLevel as il

    JOIN #DBPremissions as dbp

    ON il.in_name = dbp.MemberName

    order by il.in_name" queryout D:\test_it.txt -c -t'

    PRINT 'The actual command ' ++ @bcpCommand;

    SET @FilePath = 'D:\'

    PRINT @FILEPATH;

    SET @OutputFile = 'test_it.txt'

    PRINT @OutputFile;

    --SET @bcpCommand = @bcpCommand + @OutputFile + '" -c -t","'

    PRINT ' The excuable comand ' ++ @BcpCommand;

    EXEC master..xp_cmdshell @bcpCommand

    --Clean up

    -- Temp Table Clean up

    DROP TABLE #InstanceLevel;

    DROP TABLE #DBPremissions;

    -- CLOSE the command SHELL

    EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'xp_cmdshell', 0

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'show advanced options', 0

    GO

    RECONFIGURE

    GO

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

  • that help many show when you have problem with using bcp.

    Do you have privilege to save file on location where you want to save?

    Do you try to add user name and password on command?

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

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