Create a SQL Server Login and User with limited permissions

People always say you shouldn't use the sa account for your web app, and you shouldn't! Below is a script to make a login and user for your database with basic CRUD permissions. If your web app is compromised, damage to the database will be limited to just that database, and not to all the databases running in your SQL Server instance. While still bad, the attacker would only be able to mess with your data, and wouldn't be able to drop/create tables or other nasty things.

Assuming you have a database name "webapp1", and want a user "philipjfry" with the password "deltabrainwave":

CREATE LOGIN phillipjfry 
    WITH PASSWORD = 'deltabrainwave';

USE [webapp1];
GO

CREATE USER phillipjfry FOR LOGIN phillipjfry
    WITH DEFAULT_SCHEMA = dbo;

GRANT INSERT TO webapp1;
GRANT SELECT TO webapp1;
GRANT UPDATE TO webapp1;
GRANT DELETE TO webapp1;

GO

Published: July 12, 2013

Need hosting? I use and recommend a Digital Ocean SSD VPS server! Check out my referral link below:

Get an awesome virtual SSD server cheap!
blog comments powered by Disqus