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):
List of Server Logins
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
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:
Developers and end-users shouldn't have administrator permissions to perform their tasks. And their permissions should be managed for each database.
Fortunately, 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
Here 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.