SQL Server Login and User – Limited Permissions for CRUD Access

Securitysql servert-sql

I'm trying to create a SQL statement to create a login and user in SQL Server that will allow a web application to perform crud actions on a single database, and allowing full access to all tables and stored procedures, views etc.

I know how to create a login and a user… but I then get out of my depth as the best way to continue.

CREATE LOGIN [myUser] WITH PASSWORD=N'xxx', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER [myUser] FOR LOGIN [myUser] 
    WITH DEFAULT_SCHEMA = dbo;
GO

Best Answer

If you want a user to be able to read any table and view in your database then you would run the following from SQL Server 2012 onwards

ALTER ROLE db_datareader ADD MEMBER MyUser;

For previous versions you would run

sp_addrolemember 'db_datareader','MyUser';

db_datareader is a built in role that grants read access to everything. db_datawriter is its equivalent for writes.

If you want to grant limited CRUD access then you need to create a new role and use the GRANT statement to assign permissions to that role. After you have done that you can use the statements above to make your user a member of your new role as well.

CREATE ROLE MyLimitedCRUDRole;

For the stored procedure part of your question I would set up an explicit role for stored proc access. I would keep this separate to MyLimitedCRUDRole as the visibility of what MyLimitedCRUDRole is of increasingly high importance in a GDPR world.

I would also advise having roles that have clear and single purpose for clarity.

CREATE ROLE db_AllProcExecutor;
GRANT EXECUTE ON MyStoredProc to db_AllProcExecutor;

It is possible to generate the GRANT statements dynamically but on any database with a security sensitivity I would be very careful doing so.

Related Question