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.

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

Abhith Rajan

Written byAbhith Rajan
Abhith Rajan is an aspiring software engineer with more than nine years of experience and has a proven successful track record of delivering technology-based products and services.
Connect

Is this page helpful?

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

Related ArticlesView All

Related VideosView All

SQL Server 2017 Graph Database

Signal - the most secure messenger for everyone

Common API Security Pitfalls - Philippe De Ryck

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.