if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_defaultdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_defaultdb]
GO
exec p_defaultdb
--*/
create proc p_defaultdb
@defaultdb sysname=N'master', --默认数据库名
@setalluser bit=0 --为1,设置所有用户;非1,仅处理丢失了默认数据库的用户
as
if db_id(@defaultdb) is null set @defaultdb=N'master'
declare @s nvarchar(1000)
declare tb cursor local
for
select N'sp_defaultdb N'+quotename(loginname,N'''')+',N'+quotename(@defaultdb,'''')
from master.dbo.syslogins a
where @setalluser=1 or not exists(
select * from master.dbo.sysdatabases where name=a.dbname)
open tb
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
go