Create New Database Level User
Create a database-level user in SQL Server/Azure SQL.
commit 381ae9d · authored · 2 min read
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 dbALTER 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 name | Description |
|---|---|
| db_owner | Members 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_ddladmin | Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. |
| db_datawriter | Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. |
| db_datareader | Members 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.
