1.Logins & Server Roles
- Logins and Server Roles define users and permissions at the Instance level.
- Server Role permissions cannot be seen through the user interface, to do this you need to execute:
- If you want to grant or deny a specific permission on a database to a user you can use the following command:
USE [database] GO DENY ALTER TO [domain\user] GO
NOTE: Deleting the Logins won’t delete the Users at the database level. This will result in orphaned Users.
2.Users & Database Roles
- Users and Database Roles define users and permissions at the Database level.
- The User name can be different from the associated Login name, but this is not recommended.
- In case you have an orphaned user you can reassign the User to a Login using the command:
ALTER USER domain\user WITH LOGIN = domain\user GO
- If you want to give a User specific permissions on tables, databases, etc. you can do this with “Securables” (see the image below).
- You can use Schemas to group objects(tables, procs, views, etc.) and apply user permissions on that schema. To apply permissions use the following command:
GRANT SELECT ON SCHEMA::[dbo] TO [domain\user]
- In case you only want to permit remote access from a specific server and user, you can do this through Firewall Rules. Create a rule for each server that needs to connect to the SQL Server and specify “Remote Computers” and “Remote Users” for that each rule.
- You can also control acces to SQL through “Endpoints”. You can use endpoints to enable SQL connections on multiple TCP ports. Endpoint can be configured only through Transact-SQL.