April 18, 2012

sp_defaultdb (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_defaultdb(nvarchar @loginame
, nvarchar @defdb)

MetaData:

 create procedure sys.sp_defaultdb  
@loginame sysname, -- login name
@defdb sysname -- default db
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @exec_stmt nvarchar(4000)
declare @ret int -- return value of sp call

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_defaultdb')
return (1)
end

-- VALIDATE LOGIN NAME:
execute @ret = sys.sp_validname @loginame
if (@ret <> 0)
return (1)

-- VALIDATE DATABASE NAME --
if @defdb is null
begin
raiserror(15010,-1,-1,@defdb)
return (1)
end

BEGIN TRANSACTION
-- LOCK LOGIN --
EXEC %%LocalLogin(Name = @loginame).Lock(Exclusive = 0)

-- ADD ROW FOR NT LOGIN IF NEEDED --
if @@error <> 0 -- not found
begin
execute @ret = sys.sp_MSaddlogin_implicit_ntlogin @loginame
if (@ret <> 0)
begin
ROLLBACK TRANSACTION
raiserror(15007,-1,-1,@loginame)
return (1)
end
-- login locked
end

-- CHANGE DEFAULT DATABASE --
set @exec_stmt = 'alter login ' + quotename(@loginame) +
' with default_database = ' + quotename(@defdb)

exec (@exec_stmt)

if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end

COMMIT TRANSACTION

-- RETURN SUCCESS --
return (0) -- sp_defaultdb

No comments:

Post a Comment

Total Pageviews