A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Here is what I am looking for.
alter proc usp_login_creation
@NinjaTech varchar(100),
@NinjaTech _type varchar(20),
@dbname varchar(50),
@dbrole varchar(50)
as
begin
BEGIN TRY
-- if he is a Windows login do below
IF (@NinjaTech _type = 'WINDOWS_LOGIN')
BEGIN
IF (SUSER_ID(@NinjaTech ) IS NULL)
begin
PRINT 'CREATE LOGIN '
end
ELSE
begin
PRINT 'LOGIN ALREADY EXISTS'
end
END
-- if he is a sql authentication user do below
IF (@NinjaTech _type = 'SQL_LOGIN')
BEGIN
IF (SUSER_ID(@NinjaTech ) IS NULL)
begin
PRINT 'CREATE LOGIN '
end
ELSE
begin
PRINT 'LOGIN ALREADY EXISTS'
end
END
END TRY
BEGIN CATCH
--EXEC dbo.spErrorHandling
print 'Error...'
END CATCH
end
go
CASE 1:
Now , if I want to create a Windows Authentication Login , I want to pass below parameters and want to execute below TSQL stmts dynamically, thats where I am stuck. Can anybody help?
--Run 1
declare @v_login varchar(100)
declare @v_loginType varchar(20)
declare @v_dbname varchar(50);
declare @v_dbrole varchar(50);
set @v_login = '[ABCD-CORP\Adam]'
set @v_loginType = 'WINDOWS_LOGIN'
set @v_dbname = 'SQLDBA_utils'
set @v_dbrole = 'db_owner'
EXEC usp_login_creation @v_login,@v_loginType,@v_dbname,@v_dbrole
go
--- Below code I want to execute dynamically in a variable or so and embed the same inside above stored proc inside the create WINDOWS_LOGIN block. How do it???
/*
---- create windows
USE [master]
GO
CREATE LOGIN [ABCD-CORP\Adam] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [SQLDBA_utils]
GO
CREATE USER [ABCD-CORP\Adam] FOR LOGIN [ABCD-CORP\Adam]
GO
USE [SQLDBA_utils]
GO
ALTER ROLE [db_owner] ADD MEMBER [ABCD-CORP\Adam]
GO
*/
CASE 2: -- if it is a sql authentication login , i want to execute below code
--Run2 :
-- SQL LOGIN CREATION
declare @v_login varchar(100)
declare @v_loginType varchar(20)
set @v_login = 'test'
set @v_loginType = 'SQL_LOGIN'
set @v_dbname = 'SQLDBA_utils'
set @v_dbrole = 'db_owner'
EXEC usp_login_creation @v_login,@v_loginType,@v_dbname,@v_dbrole
go
-- For sql authentication login , I want to execute below as dynamic sql. How to do it?
USE [master]
GO
CREATE LOGIN [Smith] WITH PASSWORD=N'SunRise#123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [SQLDBA_utils]
GO
CREATE USER [Smith] FOR LOGIN [Smith]
GO
USE [SQLDBA_utils]
GO
ALTER ROLE [db_owner] ADD MEMBER [Smith]
GO
Thanks.
Sam