sql-server | security

Create New Database Level User

Create a database-level user in SQL Server/Azure SQL.

Abhith Rajan
Abhith RajanJanuary 23, 2022 · 2 min read · Last Updated:

Connect to your SQL-Server using privileged account credentials (eg. sa) in your favorite IDE. I still use SSMS together with Azure Data Studio.

Set the query target database, In most IDE, right click on the DB, then new query.

CREATE USER [username]
WITH PASSWORD = 'password'
-- add user to role(s) in db
ALTER ROLE [db_datareader] ADD MEMBER [username];
ALTER ROLE [db_datawriter] ADD MEMBER [username];
ALTER ROLE [db_ddladmin] ADD MEMBER [username];
GRANT EXECUTE TO [username]

Execute the above statement after replacing the username and password according to your choice. You can also manage the roles as per your needs.

Some of the Fixed-database roles are,

Fixed-Database role nameDescription
db_ownerMembers of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_ddladminMembers of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriterMembers of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareaderMembers of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.

Note: In order to connect to the DB using the new credentials, you need to specify the database name in the advanced options from your IDE since the above credentials don’t have the privilege to list the DB’s in the server.

Additional Resources

This page is open source. Noticed a typo? Or something unclear?
Improve this page on GitHub


Abhith Rajan

Written byAbhith Rajan
Abhith Rajan is a software engineer by day and a full-stack developer by night. He's coding for almost a decade now. He codes 🧑‍💻, write ✍️, learn 📖 and advocate 👍.
Connect

Is this page helpful?

Related ArticlesView All

Related VideosView All

Yubikey - The Ultimate Beginner Guide (How to Setup & Use)

High Availability and SLA for Azure SQL Managed Instance

Using SQL Data Sync for Bidirectional Data Replication in SQL Server & Azure SQL DBs | Data Exposed

Related Tools & ServicesView All

haveibeenpwned.com

Have i been pwned?

Check if you have an account that has been compromised in a data breach
sqlfum.pt

sequel fumpt

sqlfmt is an online SQL formatter. It is pronounced sequel fumpt. Its purpose is to beautifully format SQL statements.