October 7, 2021 at 6:36 am
Hi,
Does anybody has a script or stored procedure which takes care of login creation, user creation and adding roles and permissions.
Basically, looking for script which does the following :
We will pass loginame, login type, db name, db_rolename as 4 parameters to stored procedure/sql script.
It should check for existence of the login, if login doesn't exists, then create a windows login or a sql login based on the login type.
Also, based on the dbname which is passed as parameter or input value, it should go ahead and create the user in that database and assign the db_role passed as parameter.
Login type valid values :
WINDOWS_LOGIN, SQL_LOGIN
Thanks in advance.
Sam
October 7, 2021 at 7:19 am
That’s not that difficult to create yourself and you would learn a lot more than someone providing you a script which may or not meet your requirements.
What are you struggling with in generating this yourself?
Personally I would use PowerShell and DBATools for this as it’s just easier.
new-dbalogin
new-dbadbuser
Add-dbadbrolemember
October 7, 2021 at 10:31 am
I want a script which is plain and simple to understand. I would prefer using plain TSQL Also, looking if someone is using similar one, I want to reuse it. Here is what I am looking for.
alter proc usp_login_creation
@login varchar(100),
@login_type varchar(20),
@dbname varchar(50),
@dbrole varchar(50)
as
begin
BEGIN TRY
--SELECT 1/0
PRINT 1
select @login, @login_type
-- if he is a Windows login do below
IF (@login_type = 'WINDOWS_LOGIN')
BEGIN
IF (SUSER_ID(@login) IS NULL)
begin
PRINT '*** CREATE LOGIN ***'
end
ELSE
begin
PRINT '*** LOGIN ALREADY EXISTS***'
end
END
-- if he is a sql user do below
IF (@login_type = 'SQL_LOGIN')
BEGIN
IF (SUSER_ID(@login) 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
-- WIN LOGIN
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 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
October 7, 2021 at 4:59 pm
Any help on how below script can be dynamically executed within the stored proc?
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply