I think that many developers that work SQL Server do not have a clear understanding of this topic (I used to be one of them) and because of that is that I am writing this post.
Logins vs. User accounts
To connect to a SQL Server instance you first need a login so the instance can authenticate you (know who you are). But to perform operations on a specific database you will need a user account in that database. The user account is used for database access and permission validation. So you could have a login, but if you don’t have a user account associated to that login, you won’t be able to access that database.
Logins
To create a login you can use the following code snippet.
— To create a login that uses Windows Authentication:
CREATE LOGIN <name of Windows User> FROM WINDOWS; GO
— To create a login that uses SQL Server Authentication:
CREATE LOGIN <login name> WITH PASSWORD = ‘<the password>’ ; GO
The actions you can perform with a SQL Server instance (like create, backup and restore databases) depend on the server roles associated with your login. Once the login is created you can assign it server roles by using the [sp_addsrvrolemember] stored procedure. Possible server roles are: dbcreator, sysadmin and serveradmin among others.
User Accounts
User accounts are defined at database instance level, and every user account must be associated to a login, so to create a user account you must provide login.
— To create a user for the myLogin login for the myDatabase database
use myDatabase
create user myUser for login mylogin with default_schema=dbo
As you can see, when creating a user you don’t need to provide a password, because there is already a password associated with the login.What a user can do with the database is defined by the user’s permissions. Permissions are grouped into Roles and there are three different kind of roles:
- Database Fixed roles: are pre-defined roles by the system
- User-defined roles: are roles created by the user
- Application roles: are created to support the security needs of an application. (often database applications
enforce their own security based on the application logic)
When a user is created it is automatically added to the public (fixed) role. To add a user to a role you can use the [sp_addrolemember] stored procedure.
Hope you find this useful.
Blog de NicoPaez 