Sql Server Script to create a new user

Before we fly: User creation checks

Always ensure the user or login doesn't exist already before creating one. It's a bit like checking your parachute before a skydive!

-- "Do I have a parachute!? 👀"
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'UserLogin')
BEGIN
    -- Breathe easy, proceed with creating the new server login
END

USE DatabaseName;
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'UserName')
BEGIN
    -- Coast is clear, you can safely create the new user
END


Laying out the red carpet: Roles and Permissions

Understanding the difference between server-level and database-level roles is like knowing the difference between the Oscars and the Golden Globes- they're distinct and serve unique purposes! Use sp_addsrvrolemember for server roles, and sp_addrolemember for database roles.

-- The user's ticket to the Oscars or sysadmin role
EXEC sp_addsrvrolemember 'UserLogin', 'sysadmin';

-- Newer SQL Server versions are more like the SAG Awards
ALTER SERVER ROLE sysadmin ADD MEMBER UserLogin;


Double trouble: Mixed Mode Authentication & Access

For those fancy apps that want two types of authentication, make sure to activate Mixed Mode. Don't forget to check if your server roles like TCP/IP are set up and ready to go, much like making sure your tickets are in hand before leaving for the event. All doors (a.k.a. firewall ports) should be open for remote access.

Lean and mean code: Maintainability and Scalability

Using variables in your SQL scripts is like investing in digital measuring cups- it makes future adjustments much easier. Here's an example:

DECLARE @LoginName NVARCHAR(50) = 'UserLogin'; -- This is your A-list star
DECLARE @Password NVARCHAR(50) = 'UserPassword!'; -- Nobody needs to know this
DECLARE @UserName NVARCHAR(50) = 'UserName'; -- It's a stage name
DECLARE @DbName NVARCHAR(50) = 'DatabaseName'; -- Imagine this, but larger than Hollywood

-- Log in for your star
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'UserLogin')
BEGIN
    CREATE LOGIN [@LoginName] WITH PASSWORD = @Password;
END

-- User role in the database movie
USE [@DbName];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'UserName')
BEGIN
    CREATE USER [@UserName] FOR LOGIN [@LoginName];
    -- The roles and privileges part of the movie contract follows next
END


To infinity and beyond: Future adapting User Access

Much like the changing movie industry, SQL Server has evolved with ALTER USER and ALTER LOGIN for flexible access.

Director's cut: Fine-tuning Permissions

Detailed permissions are vital for executing functions and stored procedures, same as fine-tuning a movie script with GRANT statement:

-- "Lights. Camera. Stored Procedure."
GRANT EXECUTE ON Schema::dbo TO UserName;


Rolling on location: Network Considerations

Making the movie magic happen often requires off-site shoots, or in SQL Server terms, remote access. Setting up the proper network environment, includes necessary port forwarding and firewall configurations.