SQL Permissions

Question

Hi,

I would like to understand different database level permissions like db_owner, db_datareader etc. I am trying to get the SQL commands allowed for the these roles.

Also I would like to map users for these roles for developers, QA engineers etc

For eg developers can have the access as below

• Production server

○ None (SA/PM will apply for schema setup, end user will provide init data)	

• UAT server

○ None (SA/PM will apply for schema setup and sample data seeding)	

• Testing/QA server

○ Usually developer will send schema setup script to QA team and QA creates the tables

○ Developers have full access to databases but seldom alter it

○ Developers may help QA colleagues to seed/patch/delete some data

• Development server/localhost

Dev Server

Full access

Answer 1

Hi Santhi Dhanuskodi •,

Welcome to Microsoft Q&A forum.

As I understand, you want to understand Roles in SQL.

Is your question about SQL Server or SQL Server hosted on Azure Cloud?

Refer: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

Awaiting your reply.

Thanks

Answer 2

Hi @Santhi Dhanuskodi,

To easily manage the permissions in your databases, SQL Server provides several roles that are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.

For details about the roles, you may check Database-level roles.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

It is very difficult to say what roles you should apply in various environments, because it depends on so many things that are local to your workplace. It also depends on what sort of data you have. You can be more lax in, say, manufacturing than in healthcare or finance.

Here follows a brief discussion.

Plain users - Preferrably they should only have permission to run stored procedures, but many applications do not use stored procedures throughout. In this case you may have but the choice to grant them SELECT permssions on applicable schemas. If the application uses stored procedures for updates, you can stop there, but else you may also have to grant them INSERT. UPDATE and DELETE permissions.

You may note here that I am not using the roles db_datareader and db_datawriter. I think you should grant permissions on schema level. This permits you to put management tables and other objects in a separate schema, out of reach for the users.

db_owner - This is a very powerful role which can do anything in the database. Membership in sysadmin makes you db_owne in all databases. Whether you should add someone who is not sysadmin to db_owner in a database depends very much on the circumstances. It may off-load you as a DBA, but it also introduces risks.

db_ddladmin - Never add anyone directly to this role, but instead create your own role:

CREATE ROLE MyDdlAdmin
DENY ALTER ANY DATABASE DDL TRIGGER TO MyDdlAdmin
ALTER ROLE db_ddladmin ADD member MyDdlAdmin

Then add developers you want to work with tables, stored procedures etc to MyDdlAdmin. Developers should never have the right to work with DDL triggers.