September 30, 2013 at 11:55 am
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
--------------------------------------------------------------------------------
October 2, 2013 at 10:39 am
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