Usually there is a confusion over logins and users in SQL Server, especially for new SQL Server users. Here I have summarized the points to help understand the difference between these two concepts.
SQL Login
- SQL Login is for Authentication. Authentication can decide if we have permissions to access the server or not.
- Login is created at the SQL Server instance level.
- The logins will be assigned to server roles (for example, public, serveradmin, sysadmin etc).
-
Create Login:
CREATE LOGIN [idrees_login] WITH PASSWORD = 'password-goes-here';
-
Assign Server-Role to Login:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [idrees_login]
SQL User
- SQL Server User is for Authorization. Authorization decides what are different operations we can perform in a database. Permissions inside the database are granted to the database users, not the logins.
- User is created at the SQL Server database level. We can have multiple users from different databases (one user per database) connected to a single login to a server. User will always mapped to a Login.
- Users will be assigned to roles within that database (eg. db_owner, db_datareader, db_datawriter etc).
-
Create User:
CREATE USER [idrees_user] FOR LOGIN [idrees_login];
-
Assign Database-Role to User:
ALTER ROLE [db_owner] ADD MEMBER [idrees_user]
General:
- Logins only allow you to access to server. If the login is not mapped to any database user, then it will not be allowed to access any objects in the database.
- You can not have a (database) user without a (server) login.
- You cannot speficy the crendentials for user. Since user is mapped to a login, the login credentials are user for connection access. The same login credentials will be used to access all the databases for which a user is mapped.
- Within a database, the objects-level permissions will be granted/revoked on user.
