CREATE LOGIN MyUserName WITH PASSWORD=N'MyPassword' GO CREATE USER MyUserName FOR LOGIN MyUserName GO ALTER USER MyUserName WITH DEFAULT_SCHEMA=[dbo] GO
CREATE USER MyUserName from LOGIN MyUserName go /* Grant permission for SELECT statements */ ALTER ROLE [db_datareader] ADD MEMBER MyUserName GO /* Grant permission for INSERT, UPDATE, and DELETE statements */ ALTER ROLE [db_datawriter] ADD MEMBER MyUserName GO /* Grant permission to execute stored procs in the "dbo" schema */ --GRANT EXECUTE ON schema::dbo TO MyUserName GO
declare @Users table(Username varchar(100), Pwd varchar(25)) ;with cte as ( select username='User1', pwd = 'MyPassword1' union select username='User2', pwd = 'MyPassword2' ) insert into @Users (Username, Pwd) select Username, Pwd from cte /*--- Create Logins (Master DB) ---*/ select Sort1 = 10 ,Sort0 = username ,SqlCode = 'create login ' + Username + ' with password=N''' + pwd + '''' from @Users union select Sort1 = 20 ,Sort0 = username ,SqlCode = 'go' from @Users union select Sort1 = 30 ,Sort0 = username ,SqlCode = 'create user ' + username + ' for login ' + username from @Users union select Sort1 = 40 ,Sort0 = username ,SqlCode = 'go' from @Users union select Sort1 = 50 ,Sort0 = username ,SqlCode = 'alter user ' + username + ' with default_schema=[dbo]' from @Users union select Sort1 = 60 ,Sort0 = username ,SqlCode = 'go' from @Users order by Sort0, Sort1 /*--- Create Users (app DB) ---*/ select Sort1 = 10 ,Sort0 = username ,SqlCode = 'create user ' + Username + ' from login ' + username from @Users union select Sort1 = 20 ,Sort0 = username ,SqlCode = 'go' from @Users union select Sort1 = 30 ,Sort0 = username ,SqlCode = 'alter role [db_datareader] add member ' + username from @Users union select Sort1 = 40 ,Sort0 = username ,SqlCode = 'go' from @Users union select Sort1 = 50 ,Sort0 = username ,SqlCode = 'grant execute on schema::dbo to ' + username from @Users union select Sort1 = 60 ,Sort0 = username ,SqlCode = 'go' from @Users order by Sort0, Sort1
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.