Microsoft Azure and SQL ServerMicrosoft SQL ServerMicrosoft 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).

Contents


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

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.

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:

Microsoft SQL Server Database ContainmentThis may trigger an error that prevents you from modifying this for an existing database:

Microsoft SQL Server Database Fails to Change Containment to Parial

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:

Microsoft SQL Server Properties Advanced Containment Enable Contained Databases

Then set the database containment to Partial again.

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

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

Database Users


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

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.

SQL Server Management Studio (SSMS)

From SSMS, click on the Options button on the Login to specify the database name on the Connection Properties tab:

SQL Server Management Studio (SSMS) Database Name Must by Specified on Connection Properties tab

ODBC Data Sources

Similarly, after specifying the SQL Server name, enter the database name:

ODBC SQL Server Database Name Must by Specified

Can't Create File DSN for Database Users Hosted on SQL Azure

We've encountered problems creating File DSN with database user names.

From the ODBC Data Source Administrator, if you choose File DSN:

ODBC Data Source Administrator File DSN

After stepping through the screens, it triggers a login connection error like this:

Microsoft SQL Server Login Connection Failed SQLState: '28000'

Microsoft SQL Server Login
Connection Failed
SQLState: '28000'
SQL Server Error: 18456

Solution

Choose the User DSN or System DSN tabs instead of File DSN.

Thanks!

Special thanks to my colleague Philipp Stiefel of Germany who helped me refine the details of this paper.


Free Product Catalog from FMS