Microsoft SQL Server Database Users and Permissions Instead of Server Logins

Written by: Luke Chung, President

This information applies to Microsoft SQL Server hosted on your own platform and Microsoft Azure. It assumes you are familiar with SQL Server Management Studio (SSMS).

Windows Authentication versus Server Logins
Microsoft SQL Server Security Logins
Database Users Can Only Be Defined for Contained Databases
Database User Permissions
Connecting to Databases with these Database Permissions
Microsoft SQL Server Login Connection Failed

Windows Authentication Versus Server Logins

Microsoft SQL Server database connections can be through Windows Authentication or a login with a user name and password.

Windows authentication doesn't require a user name and password because Windows and SQL Server automatically recognize the current user and grants them the permissions that are assigned to that user.

Windows Authentication is NOT Secure for Applications that Modify Data or Hold Confidential Information

If your application modifies data, it probably has validation rules to ensure the data is updated properly. It would also control who and how records are added and deleted, executing stored procedures, etc.

If the application relies on Windows authentication for security and grants users those permissions, your application will perform its tasks for the Windows users who are allowed to connect to the database.

However, that also means your users can also run Access, Excel and other programs to connect to your SQL Server database and perform the same tasks against your tables outside your application.

You may want your users to use the data in ReadOnly mode, and even then, you may not want them to see data that should be confidential. If your application relies on Windows authentication, you cannot control or hide data and stored procedures that the application needs but the user should never modify or see on their own.

For purposes of this paper, we are NOT considering Windows authentication

Microsoft SQL Server Security Logins

By default, the database server has a login with administrator permissions. This login allows:

Creation and deletion of databases
Permissions to make modifications to databases including objects and data modifications

One can use this login to do anything with any database hosted by that server. It makes it easy for developers and users to create connection strings to link to all the databases and tables on that server.

Security Problem

Having one login for all the resources of a server is a security problem. Anyone with it can automatically use and destroy all the databases hosted there.

There are distinct sets of users and tasks that should be separated, especially if there is more than one person involved:

SQL Server administrator to add and delete databases
Developers to create, design and delete database objects
Applications that need to view and edit data
End-users who need to edit data
Read only end-users who need to use data but not modify it

Developers and end-users shouldn't have server administrator permissions to perform their tasks. And their permissions should be managed for each database.

Fortunately, we can create database users to handle this which have the added benefit of working if the database is moved to another server.

Database Users Can Only Be Defined for Contained Databases

Before we create database users, the database must be "contained".

Not an Issue for SQL Azure

Databases created on SQL Azure are always contained so this section doesn't apply to you.

Your Own SQL Server Host

With your own SQL Server, it may or may not be contained. From SQL Server Management Studio, you can set this when creating a new database, or right click on an existing database and choose Properties. Under Options, change the containment type from None to Partial:

This may trigger an error that prevents you from modifying this for an existing database:

You may run the equivalent code and trigger the same error:

ALTER DATABASE Sample SET Containment = PARTIAL

Msg 12824, Level 16, State 1, Line 1
The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

This can be fixed from SSMS by changing the Server properties. Under Advanced, Containment, changed Enable Contained Databases to True:

Then set the database containment to Partial again.

For more information SQL Server Contained Databases read Security Best Practices with Contained Databases

Database User Permissions

Introduction

Fortunately, once your database is contained, Microsoft SQL Server makes it easy to create and manage database permissions:

Create a user name with a password
Assign permissions to the user name

The permissions you grant are called roles. There are many roles beyond the three shown below. You can add multiple roles to each user and deny permissions at the object level.

For more information, visit this Microsoft resource: SQL Server Database Roles

Below are some basic user definitions.

Full Database Permissions (Owner)

The database developer needs full rights to make modifications to the database objects. That's defined as the database owner.

From the database, create a New Query and execute this syntax to create a user named ownerName with a password (note 123456 is not complex enough). The user name is then granted owner permissions:

CREATE USER ownerName WITH PASSWORD = '123456'
GO
sp_addrolemember 'db_owner', ownerName

Note that the owner role allows deleting (dropping) the database. This is generally considered okay since they already have permissions to delete all the objects in the database.

User vs. Login

Note that the syntax is for USER not LOGIN. SQL Server defines users for the database level while logins are for the server.

End User Data Permissions for Editing and Read Only

End-users who need to view and edit data shouldn't have permissions to modify the table structures and other objects. For these uses, one should have two user names:

Read Only Permissions (Reader)

To only view (read) data in user tables (SQL SELECT statements):

CREATE USER userReader WITH PASSWORD = '123456'
GO
sp_addrolemember 'db_datareader', userReader

Editing Permissions (Writer)

To add, delete and change data in user tables, they need WRITER permissions. Note that if they also need to run SELECT queries (almost always), they also need to have read permissions:

CREATE USER userWriter WITH PASSWORD = '123456'
GO
sp_addrolemember 'db_datawriter', userWriter
GO
sp_addrolemember 'db_datareader', userWriter

If you want the user to run stored procedures, use the GRANT EXECUTE command:

GRANT EXECUTE TO userWriter
GO
GRANT EXECUTE TO userReader

After creating the users, they'll appear under the database's Security, Users' folder

Additional Commands

Here are some other helpful commands for managing user names:

Delete a User Name

You can use SSMS to locate the user name, right click and delete, or use this syntax:

DROP USER fmsinc

Change a User's Password

ALTER USER userName WITH PASSWORD = 'New Password'

Remove Permissions to Run Stored Procedures

REVOKE EXECUTE TO userName

You can also run a query to get the list of users:

SELECT * FROM sys.database_principals WHERE Type = 'S'

Additional Reference

For more information on database users, visit this Microsoft page: Contained Database Users - Making Your Database Portable

Connecting to Databases with these Database Permissions

When connection strings use the database user names and passwords, the permissions are limited to that database and they cannot see other parts of the server.

That means when you use a program like ODBC Data Sources or SQL Server Management Studio (SSMS), you need to specify the database name because the login cannot retrieve the server's list of databases.