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. Assumes you are familiar with SQL Server Management Studio (SSMS).

Connecting to a Microsoft SQL Server database requires a login which includes a user name and password.

By default, the database server has a login with administrator permissions. This login allows the creation and deletion of databases. It also provides full permissions to make modifications to databases including objects and editing data.

Server logins can be created using syntax like this (in a New Query):

CREATE LOGIN fmsinc WITH PASSWORD = '123456#%$@#lkjlskdjf'

From SQL Server Management Studio, you can see the list of the server's logins under the Security, Logins folder (right click on Logins and select Refresh to see recent changes):

Microsoft SQL Server Security Logins

List of Server Logins

Additional Commands

You can run a query to get the list of all server logins:

SELECT * FROM sys.sql_logins

From SSMS, you can select a login name in the Logins folder, right click and delete it, or use this syntax:

DROP LOGIN fmsinc

Security Problem

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.

However, having one login for all the resources of a server is a security problem. There are distinct classes of users and tasks which should be separated if there's more than one person involved:

  • Server level permission to add and delete databases
  • Developer's need to create, design and delete database objects
  • End-users who view and edit data
  • Read only End-users who can only view data but not edit anything

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

Introduction

Fortunately, 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

Here 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 dbOwner with a password (note 123456 is not complex enough). The user name is then granted owner permissions:

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

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

With database user names and passwords, when you provide connection strings to others, their permissions are limited to what you expect.

When you use a program like ODBC Data Sources or SQL Server Management Studio with these database logins, you need to specify the database name.

A list of all the databases on the server doesn't exist since the user name is limited to the specific database and you need to provide it.

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.


Strategic Overview

SQL Server Express

SQL Azure Cloud

What it Means to Information Workers

Link Microsoft Access to SQL Azure Databases

Deploy MS Access DBs linked to SQL Azure

Eliminate SQL Azure Security Holes

Monitor SQL Server Usage and DTU Limits on Azure

Convet Azure SQL Server to Elastic Pools

SQL Server Tips

Database Users and Permissions

Database Backup and Restore on Network

AM/PM Date Time Format

Get Date Portion

Set NOCOUNT ON

Running Total

Videos

Migrate Your Data Tier to SQL Server: Strategies for Survival

Microsoft Access Database and Migration Challenges

Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Services

Microsoft SQL Server Consulting Services

Visual Studio .NET programmers for web development

General Microsoft Access consulting services

Additional Resources

Microsoft Access Help

Microsoft Access Developer Help Center

MS Access Developer Programming

More Access and SQL Tips

Technical Papers

Microsoft Access Tools

Connect with Us

 

Free Product Catalog from FMS