2

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
0
5

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.

1
  • 1
    To grant EXECUTE on all stored procedures just run GRANT EXECUTE TO MyLimitedCRUDRole. To limit to one schema run GRANT EXECUTE ON SCHEMA::DBO TO MyLimitedCRUDRole. Same thing works on INSERT, UPDATE, DELETE and SELECT. So GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::DBO TO MyLimitedCRUDRole is all you need. – David Browne - Microsoft Apr 18 '18 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.