June 8, 2012

sp_SetAutoSAPasswordAndDisable (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_SetAutoSAPasswordAndDisable()

MetaData:

 create procedure sys.sp_SetAutoSAPasswordAndDisable  
as
-- can execute only as SysAdmin
if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it is the SA executing this.
begin
raiserror(15247,-1,-1)
return(1)
end

-- Begin a transaction
BEGIN TRANSACTION

declare @stmt nvarchar(4000)
declare @sa nvarchar(128)

SELECT @sa = name
FROM sys.server_principals WHERE sid = 0x01

-- Disable Password Policy on the SA Login
SELECT @stmt = 'ALTER LOGIN ' + quotename(@sa) + ' WITH CHECK_POLICY = OFF '
EXEC(@stmt)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN (1)
END

-- Create a New Guid as the random password
declare @randompwd UNIQUEIDENTIFIER

SET @randompwd = newid()
SELECT @stmt = 'ALTER LOGIN ' + quotename(@sa) + ' WITH PASSWORD = ' + quotename(@randompwd, '''')
EXEC(@stmt)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN (1)
END

-- Now set the policy back
SELECT @stmt = 'ALTER LOGIN ' + quotename(@sa) + ' WITH CHECK_POLICY = ON '
EXEC(@stmt)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN (1)
END

-- Now set the policy back
SELECT @stmt = 'ALTER LOGIN ' + quotename(@sa) + ' DISABLE '
EXEC(@stmt)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN (1)
END

-- Commit the transaction
COMMIT TRANSACTION

No comments:

Post a Comment

Total Pageviews