April 25, 2012

sp_grantlogin (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_grantlogin(nvarchar @loginame)

MetaData:

 create procedure sys.sp_grantlogin  
@loginame sysname
AS
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int -- return value of sp call
declare @exec_stmt nvarchar(4000)

-- CHECK PERMISSIONS --
IF (not is_srvrolemember('securityadmin') = 1)
begin
dbcc auditevent (105, 1, 0, @loginame, NULL, NULL, NULL, NULL, NULL, NULL)
raiserror(15247,-1,-1)
return (1)
end

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

-- DISALLOW SQL LOGIN (IE. MUST BE 'DOMAIN\USER') --
if (charindex('\', @loginame) = 0)
begin
raiserror(15407, -1, -1, @loginame)
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
-- In case, the login has been created in the window,
-- try to lock the login name. We are fine if we succeed.
--
EXEC %%LocalLogin(Name = @loginame).Lock(Exclusive = 0)
if @@error <> 0
begin
ROLLBACK TRANSACTION
raiserror(15401,-1,-1 ,@loginame)
return (1)
end
end
-- login locked
end

-- GRANT ACCESS --
EXEC %%LocalLogin(Name = @loginame).SetAccess(HasAccess = 1)

COMMIT TRANSACTION

-- FINALIZATION: RETURN SUCCESS/FAILURE
return (0) -- sp_grantlogin

No comments:

Post a Comment

Total Pageviews