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).
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.
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.
By default, the database server has a login with administrator permissions. This login allows:
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.
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:
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".
Databases created on SQL Azure are always contained so this section doesn't apply to you.
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
Fortunately, once your database is contained, Microsoft SQL Server makes it easy to create and manage database permissions:
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.
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.
Note that the syntax is for USER not LOGIN. SQL Server defines users for the database level while logins are for the server.
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:
To only view (read) data in user tables (SQL SELECT statements):
CREATE USER userReader WITH PASSWORD = '123456' GO sp_addrolemember 'db_datareader', userReader
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
Here are some other helpful commands for managing user names:
You can use SSMS to locate the user name, right click and delete, or use this syntax:
DROP USER fmsinc
ALTER USER userName WITH PASSWORD = 'New Password'
REVOKE EXECUTE TO userName
You can also run a query to get the list of users:
SELECT * FROM sys.database_principals WHERE Type = 'S'
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.
We've encountered problems creating File DSN with database user names.
From the ODBC Data Source Administrator, if you choose File DSN:
After stepping through the screens, it triggers a login connection error like this:
Microsoft SQL Server Login
SQL Server Error: 18456
Choose the User DSN or System DSN tabs instead of File DSN.
Special thanks to my colleague Philipp Stiefel of Germany who helped me refine the details of this paper.