Create a SQL Server Admin on a VPS (Secure Setup Guide)

If you run Microsoft SQL Server on a VPS, giving someone “admin” access sounds simple - until you hit authentication modes, server roles, firewalls, and security audits. This guide shows you exactly how to create an administrator in SQL Server the right way, on Windows or Linux, with guardrails that hold up under compliance and production pressure.

SQL Server separates identity at two levels: server and database. A login exists at the server level (it can be Windows-authenticated or a SQL-authenticated login). A user exists inside each database and maps to a server login. Confuse those and you’ll end up with “orphaned users,” failed logins, or over-permissioned accounts.

When people say “give me admin,” they often mean sysadmin, the server-level role with total control. But sysadmin bypasses all permission checks. It’s a break-glass role you should reserve for a tiny number of trusted identities. Most maintenance or developer tasks don’t need sysadmin. Often, securityadmin (manage logins) and dbcreator (create DBs) are enough at the server level; inside a specific database, db_owner grants full control without giving the keys to the whole instance.

Before you create another all-powerful account, define the job to be done. Is it database schema changes for one database? Data import across a few databases? Instance-level configuration? Your answer drives the smallest set of roles that still lets work happen. Principle of least privilege is the difference between a normal Tuesday and a weekend breach report.

First, confirm your environment details. Are you on Windows Server or Linux? Which SQL Server edition and version? What’s the instance name - default or a named instance? Is the service reachable on the default TCP port 1433 or a custom port? These basics determine which tools and commands you’ll use.

Next, check how you can access the box. On Windows, you likely have RDP and SQL Server Management Studio (SSMS) available. On Linux, you’ll use SSH and sqlcmd or Azure Data Studio. Also verify whether Windows Authentication is possible (e.g., domain-joined server) or if you must rely on SQL logins.

Finally, confirm you have a way to connect as an existing admin to do the setup - Windows domain admin, local administrator with service rights, or the built-in sa (if enabled). If all admin paths are blocked, you’ll need to restart in single-user mode to recover access, which is outside the happy path covered here.

SQL Server supports Windows Authentication, SQL Server Authentication, or Mixed Mode (both). On a VPS used by external contractors or apps that aren’t domain-joined, Mixed Mode is common so you can create SQL logins with passwords. If your VPS is domain-joined and you manage identities via Active Directory, Windows-only is stronger.

On Windows, you can flip modes in SSMS. Connect with an admin, right-click the server node, choose Properties, then Security. Select SQL Server and Windows Authentication mode for Mixed Mode, or Windows Authentication mode to lock it down. Click OK and restart the SQL Server service. You can also change it in the registry and restart, but SSMS is less error-prone.

On Linux, use mssql-conf to enable Mixed Mode by setting a strong SA password during setup or later, then restart the service. Example:

# Enable (or reconfigure) authentication mode on Linux
sudo /opt/mssql/bin/mssql-conf setup
# Follow prompts; set a strong SA password when asked
sudo systemctl restart mssql-server

If you prefer GUI steps, SSMS makes it straightforward. Connect to your instance as an existing admin. In Object Explorer, expand Security, right-click Logins, and select New Login. Choose either Windows Authentication (for a domain or local Windows account) or SQL Server authentication with a strong password.

For SQL-authenticated logins, tick Enforce password policy and Enforce password expiration if your OS policy is sane. Set Default database to the most-used database for that account to avoid accidental work in master. Consider Deny access to the server unchecked (obviously) and ensure the login is enabled.

Click Server Roles on the left. Unless you have a clear reason for sysadmin, assign the smallest role set that fits the job. For a broad administrator who’s not the final break-glass account, securityadmin plus dbcreator may be enough; pair that with db_owner inside specific databases. Hit OK to create the login. If database access is needed, map the login to users under the User Mapping page.

If you’re running headless on Linux or you just move faster in SQL, use T‑SQL. Here’s a common flow for a SQL-authenticated admin that should manage multiple DBs without full sysadmin:

-- 1) Create a strong SQL login
CREATE LOGIN ops_admin
WITH PASSWORD = 'REPLACE-With-A-Long-Unique-Password!2026',
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON;
GO

-- 2) Grant carefully chosen server roles
ALTER SERVER ROLE securityadmin ADD MEMBER ops_admin; -- manage logins
ALTER SERVER ROLE dbcreator     ADD MEMBER ops_admin; -- create databases
GO

-- 3) Map to a database and grant db_owner for that DB only
USE YourDatabase;
GO
CREATE USER ops_admin FOR LOGIN ops_admin;
EXEC sp_addrolemember 'db_owner', 'ops_admin';
GO

-- 4) Verify permissions
SELECT l.name AS login_name, r.name AS server_role
FROM sys.server_role_members srm
JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
JOIN sys.server_principals l ON srm.member_principal_id = l.principal_id
WHERE l.name = 'ops_admin';
GO

If you truly need an all-powerful account for break-glass scenarios, grant sysadmin explicitly and document it:

ALTER SERVER ROLE sysadmin ADD MEMBER ops_admin;
GO

Finally, if the login needs access to multiple databases, repeat the CREATE USER and db_owner membership in each target DB. Keeping server-level roles lean and scoping database ownership is your best insurance against accidental blast radius.

VPS instances often sit closer to the open internet than on-prem servers. A secure admin account is only as strong as the surface around it. Start with passwords: require long, random, unique phrases; enable password policy and expiration for SQL logins; rotate credentials for contractors when SOWs end.

Lock down the network. If SQL Server doesn’t need to be public, restrict inbound 1433 to your office IPs or a VPN. Consider moving off the default port to reduce drive-by noise (security through obscurity isn’t protection, but it cuts log spam). Disable SQL Browser if you’re not using named instances or UDP-based discovery.

Harden the service and protocol. Use TLS encryption for SQL connections; issue and bind a proper certificate so clients validate the server. Turn on login auditing (failed and successful) and ship logs to a central destination. Keep the OS and SQL Server patched - most compromises still come through known, unpatched issues.

Good ops means you can answer: who can do what, where, and why. Define role profiles: a platform operator who can manage logins and create databases (securityadmin, dbcreator), a lead developer with db_owner in specific databases, and a true break-glass admin on call with sysadmin for incidents. Make these profiles explicit so requests don’t default to “just give me sysadmin.”

Enable auditing. In Standard and above, SQL Server Audit lets you track server- and database-level actions. Capture login changes, role grants, and schema changes. Even if you start with lightweight audits, have a place to look when something breaks. Extended Events can fill gaps where Audit isn’t enough.

Plan the access lifecycle. When someone joins, provision the smallest profile that lets them work. When they switch teams or finish a project, trim or remove access. When they leave, revoke access fully and rotate any shared credentials. An admin account you don’t actively manage becomes an attack surface you forgot you owned.

If you’re running a lean team and still shipping real work, this is where the operator model helps. At Anton Chisliuk · AI Marketing Ops, one senior operator runs strategy and execution - SEO, outbound, HubSpot, automation - without the agency tax or account-manager layer. Proof matters: for Cleverence (B2B SaaS) we went 0→80K organic visits/month and 0→2K inbound leads/month. The same discipline we apply to credentials, logging, and repeatable workflows is what keeps growth systems safe at speed. Learn more on our about page.

Login failed for user ‘X’ (Error 18456): check the state code. State 5 often means invalid user; State 6 can be attempted Windows login with SQL auth; State 7 is wrong password; State 8 is password mismatch; State 38 suggests the default database is unavailable - set a valid default or fix DB state.

Cannot connect to server: verify the instance is listening (telnet or Test-NetConnection to the SQL port), Windows Firewall or iptables rules allow inbound, and that you’re targeting the right instance name and port. If using a named instance, try specifying the resolved TCP port explicitly and consider disabling SQL Browser if you can hardcode ports.

Existing principal or orphaned users: if CREATE USER fails with “already exists,” map the login instead. If a database user is orphaned (login SID mismatch), remap it: ALTER USER [user] WITH LOGIN = [login]; On very old versions, sp_change_users_login exists, but it’s deprecated - use ALTER USER on supported versions.

On Windows Server VPS, SSMS is the fastest way to operate if you have RDP. Keep RDP locked down to known IPs or behind a VPN, enforce MFA on jump boxes, and separate OS-level admins from SQL admins where possible. Use NTFS permissions and service accounts wisely so backups and maintenance jobs run without embedding high-privilege creds everywhere.

On Linux VPS, the mssql-server packages are mature and stable. You’ll use sqlcmd or Azure Data Studio for admin tasks. Pay attention to SELinux/AppArmor where applicable, and keep systemd units configured for automatic restarts on failure. Store configuration overrides in /var/opt/mssql/mssql.conf and script your initial role setup so you can rebuild quickly.

Cloud VPS (Lightsail, DigitalOcean, Azure VMs, etc.) adds a network layer. Restrict the provider firewall (security groups) before exposing SQL. Automate snapshots and offsite backups. Prefer private networking when app and DB live in the same provider region to avoid public exposure entirely.

Some agencies still ask for full sysadmin “just in case.” That’s lazy risk. A professional can tell you exactly which permissions they need. If a vendor can’t operate without blanket access, that’s a signal to evaluate alternatives. Compare trade-offs in our agency vs operator comparison and choose the model that preserves control without slowing delivery.

Document ownership and purpose. Who owns the account? What task requires sysadmin versus a lesser role? What’s the rollback if it goes wrong? If you can’t answer in one paragraph, you probably don’t need sysadmin.

Establish a break-glass account separate from day-to-day identities. Store the credentials in a secrets manager with dual-control access. Test the process quarterly so you’re not learning it during an outage.

Wrap SQL in the broader safety net: jump hosts with MFA, IP allowlists, centralized log shipping, and backups that are both automated and restore-tested. If your governance posture is shaky, start there before you mint new superusers.

Creating an admin user in SQL Server is straightforward when you separate the job to be done from the impulse to hand out sysadmin. Pick the right authentication mode, create a login, scope roles to the smallest needed set, and harden the VPS around it. Do this once, script it, and your next environment takes minutes, not hours.

If you want this level of discipline across your growth systems - content, ads, CRM, and automation - book a free Growth Audit. We’ll show you where access, processes, and outcomes break - and how to fix them without adding headcount.

Written by Anton Chisliuk - Leads, sales, content, and systems: one senior operator.

- Should I enable the built-in sa account?

You can, but you don’t have to. If you enable sa, set a very strong password, rotate it, and disable it when not needed. Many teams prefer a named break-glass login with sysadmin so auditing clearly shows who used it and when.

- Is Windows Authentication more secure than SQL Authentication?

Generally yes, because Windows Auth integrates with domain policies and avoids password transmission. On a standalone VPS without domain integration, SQL Auth is often necessary. Mixed Mode lets you support both while you migrate toward stronger identity controls.

- What minimum roles should an “admin” have?

There’s no universal minimum. For instance-level config and login management, use securityadmin (and maybe serveradmin or dbcreator). For full control inside a database, grant db_owner at the database level. Reserve sysadmin for break-glass or a very small SRE group.

- How do I force encrypted connections to SQL Server?

Install a valid TLS certificate, bind it in SQL Server Configuration Manager (Windows) or configure via mssql.conf (Linux), then set Force Encryption to Yes. Update clients to trust the issuing CA and require encryption in connection strings.

- Why can’t my new admin login access a database?

You created a server login, but not a database user. Map it: USE YourDB; CREATE USER [login] FOR LOGIN [login]; EXEC sp_addrolemember 'db_owner', 'login'; Also confirm the default database exists and is online.

- Is changing the default port from 1433 worth it?

It reduces noisy scans and brute-force attempts but isn’t a security control by itself. Use it as a hygiene step alongside IP allowlists, TLS, and strong credentials. Always document the port to avoid connection confusion later.

- Can I create an admin on Linux without GUI tools?

Yes. Use sqlcmd or Azure Data Studio remotely. The T‑SQL in this guide works the same on Linux as on Windows. Ensure the service is listening, firewall rules permit your admin IP, and your authentication mode is configured correctly.

- Where can I see a proof point for disciplined ops at scale?

See our Cleverence case: 0→80K organic visits/month and 0→2K inbound leads/month - achieved with repeatable systems and tight ops. You can review the story on our site’s case studies section.