In this video we cover the basics of Security in SQL Server 2008. We go over:
Principal: Entity that can request SQL Server resources
Server level principal: Can be Windows or SQL Server
Database level principal: Can be database user/role/application role
Roles in SQL Server are the same as groups in Windows OS
Sysadmin server role is a Super user in MS SQL 08
Create a new login in SQL Server and setup permissions
Login -- New Login
SQL Server authentication
Database user mapped to login -- Sales database
Tried to login as the user and executed a SELECT query
The SELECT permission was denied
Went back and added the database user to the db_datareader role
Now the select statement works!!
Next we try to insert new data into SQL Server table.