我有一个sql语句,想把各个数据库中guest用户是否禁用输出到文件中去,但不知该怎样弄,只知道将其打印出来,期待高手帮忙搞定,谢谢!
--查询guest用户是否禁用
declare @id int
declare @idx int
declare @enabled int
declare @dbname varchar(32)
declare @sql nvarchar(255)set @idx = 3
select @id =max(database_id)from master.sys.databases
while (@idx <= @id)
begin
set @sql = 'select @tmp = hasdbaccess from ' + db_name(@idx) +'.sys.sysusers where name like ''guest'''
exec sp_executesql @sql, N'@tmp int output ', @enabled output if (@enabled = 1)
begin
print 'Guest user is enabled for database ' + db_name(@idx)
end
else
begin
print 'Guest user is disabled for database ' + db_name(@idx)
end
set @idx = @idx +1
end
--查询guest用户是否禁用
declare @id int
declare @idx int
declare @enabled int
declare @dbname varchar(32)
declare @sql nvarchar(255)set @idx = 3
select @id =max(database_id)from master.sys.databases
while (@idx <= @id)
begin
set @sql = 'select @tmp = hasdbaccess from ' + db_name(@idx) +'.sys.sysusers where name like ''guest'''
exec sp_executesql @sql, N'@tmp int output ', @enabled output if (@enabled = 1)
begin
print 'Guest user is enabled for database ' + db_name(@idx)
end
else
begin
print 'Guest user is disabled for database ' + db_name(@idx)
end
set @idx = @idx +1
end
FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=no;Delimited=";";DATABASE=C:\' --c:\是目录
,cpbuyaa20070821#txt)
-------------本例从指定的数据文件中导入订单详细信息,该文件使用竖杠 (|) 字符作为字段终止符,使用 |\n 作为行终止符。BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
GOIF OBJECT_ID('temp') IS NOT NULL
DROP TABLE temp
CREATE TABLE temp
(
GuestPrivilege VARCHAR(8000)
)
GO--查询所有数据库Guest的状态并插入临时表temp
SET QUOTED_IDENTIFIER OFF
EXEC sp_msforeachdb "INSERT INTO tempdb..temp SELECT 'Guest user is'
+ CASE WHEN hasdbaccess=1 THEN 'enabled' ELSE 'disabled' END
+ 'for database (?)' FROM ?.sys.sysusers WHERE name = 'guest'"
GO
--SELECT * FROM tempdb..temp--默认是禁用了xp_cmdshell,所以要打开
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', '1';
RECONFIGURE WITH OVERRIDE;-- -S 表示服务器名称 -T表示信任连接
-- localhost 你要替换成你自己的SQL服务器名称,保持到了d:\Message.txt(可更改)
EXEC master..xp_cmdshell 'bcp "tempdb..temp" out d:\Message.txt -c -Slocalhost -T'
GO
EXEC sp_configure 'xp_cmdshell', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE WITH OVERRIDE;
GO
--删除temp
IF OBJECT_ID('temp') IS NOT NULL
DROP TABLE temp
GO
BULK INSERT 库名..表名
EXEC master..xp_cmdshell @cCMD,no_output