How to create a user with admin access in SQL Server?

There are two realms of security in SQL Server, the server and the database. To work on the database, you must have access to the server first, then you need access to the database.

A Login grants the principal entry into the server.
A User grants a login entry into a single database.
One Login can be associated with many users (one per database).

Note that only users with ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role can create logins.

So the steps to create a new user with admin access will be as follow:

1. Create a new database, if needed

CREATE DATABASE [db_name];

2. Create a new login

CREATE LOGIN [login_name] WITH PASSWORD = [StrongPassword];

3. Then assign the admin role to the new login:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [login_name];

4. Now create a new user associated with the new login and access to the new database

USE [db_name];

CREATE USER [user_name] FOR LOGIN [login_name];

ALTER USER [user_name] WITH DEFAULT_SCHEMA=[dbo];

ALTER ROLE [db_owner] ADD MEMBER [user_name];

5. When you no longer need the newly created login, user, and database

Drop everything:

USE master;

DROP DATABASE [db_name];

DROP USER [user_name];

DROP LOGIN [login_name];

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple databases including SQL Server, MySQL, PostgreSQL, SQLite, etc. And it’s free to use for as long as you need it to.