sql-server | security

Create New Database Level User

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

Edson Frainlar
Edson FrainlarJanuary 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.

1CREATE USER [username]
2WITH PASSWORD = 'password'
3-- add user to role(s) in db
4ALTER ROLE [db_datareader] ADD MEMBER [username];
5ALTER ROLE [db_datawriter] ADD MEMBER [username];
6ALTER ROLE [db_ddladmin] ADD MEMBER [username];
7GRANT 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


Edson Frainlar

Written byEdson Frainlar
Mission-driven Full-stack Developer with a passion for developing KTern, Dev Collaboration, and teaching. Curious to explore Quantum Information and Computing.
Connect

Is this page helpful?

Related ArticlesView All

Related VideosView All

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

Build fast, scalable data system on Azure SQL Database Hyperscale | Clearent

SQL Server 2017 Graph Database

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.