I just want to talk briefly about how we can access sql server instance running either on your personal computer or on a network. Authentication is a big issue for any server because if some successfully passed this gate, they have access to your server and can do lot of things so SQL server provides options to allow administrators decide how people log onto the server. You can choose the default type of authentication at installation of the server or change it any time after installation. Lets take a look at the type of authentication we have for Microsoft SQL Server.
Windows Authentication
When a user connects through a windows user account, SQL server validates the account name and password
using the windows security in the operating system.The user identity is confirmed by windows. SQL server does not ask for the password, and does not perform the identity validation. Windows authentication is the default authentication. Its much more secure than SQL sever Authentication because Windows Authentication uses Kerberos security protocol which provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts
the credentials provided by Windows. This simply means you can only log into the server using a computer on which you have an account and that account is allowed to access the server. The problem with this authentication is clear, you can’t just log into the server without using your windows account like from another computer without your account established on it.
SQL server authentication
In this approach, the administrator has to create log ins in sql server that are not based on Windows user accounts. Both the user name and password are created by using sql server and stored in sql server. Users connecting using sql server authentication must provide their credentials(login and password) when they connect. This type of authentication allows for users to use two modes of authentication to access the server the modes are
Windows authentication mode
Only windows authentication is allowed and SQL server authentication is disabled
Mixed mode authentication
Both windows and SQL server authentications are allowed. That means, the server can allow you to log in using your windows account or you can log in using a SQL server created log in and password. This gives you both of the two worlds. Now before users are able to log into the server, the administrator has to create login that they will use. This can be either Windows Authentication Login or SQL server Authentication login. And one login can be used by more than one person. So for example, you create a login called managers. This login can be given to all managers to use to log into the server. Another thing we’ll talk about is the roles that are mapped to the login to restrict what each login can and can not do on the server objects. First lets look at how to create a log in. To create a log in, the administrator uses either TSQL or SQL Management Studio which provides easy graphical interface for doing it. You can create logins under the SECURITY section in SSMS of the server. But lets look at the code to create a log in using TSQL.
Create a windows authentication login for managers
USE [master]
GO
CREATE LOGIN [domainnamemanager] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
Create a SQL Server Authentication login for managers
USE [master]
GO
CREATE LOGIN [managers] WITH PASSWORD=N’123′ MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
What is going on?
We see that, we have to always switch to the master database when creating a login because that is the database that stores all user information and authenticates users when they first log onto the server. There are four default visible databases and one hidden database that comes with SQL server installation. They are master, model, msdb, tempdb and resource. Master contains the database system catalogs and data particular to each database, model contains a template for newly created user databases, tempdb database is used for storing temporary data and for sort operations. tempdb is always empty at start up. msdb contains the data for the SQL Server Agent (jobs, alerts and replication). The last one is Resource which is invisible in SSMS. The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. Now you see the options that come with the login, the windows one was created from windows so anyone accessing the server with that account on their system will be able to use the windows authentication to log in. Before you create the windows account, you have to make sure that account is created on the machine running the server. You can create a new account for example in Windows 7 by right clicking ‘Computer’, select manage from the pop up menu and in the window that comes (Computer Management), click on local users and groups in the left pane and it will show Users and Group folder. Right click the User folder and create a new user with the name and password that they can use to access the server. Then you can go into the server and create a windows authentication account using this user created. You have to specify the domain name of the computer when creating the account in SQL server. For example, if l’m creating the manager account on my computer. l’ll give it the name of the account in SQL server as VINCE-PCmanager, where VINCE-PC is the domain of my computer. The domain name is basically your computer name. You can find out your domain name for example in Windows 7 by right clicking computer and select properties. When the property windows comes up, look at the section that says Computer name. You can change the domain name any time. But when you do that, you have to go back into SQL server and specify the correct domain name for windows accounts you’ve created with the older domain name to point to the new domain name. You can see we do not specify password when creating the windows login in SQL server because that is already taken care of when we created users at the windows level.
For SQL server authentication logins, there is no domain name and the name doesn’t have to have a windows account. You have to provide password to be used by the login and there are option to make the password expire over some time. Now, once you’ve created this account, you can give it to another person who can access the server from any computer and enter into your server using that login information. How will they be able to access the server? That is a big question. It depends on what is the situation. For example, lets say, I have a SQL server that stores all 2smart4school data and l’m giving permission for my employees to access this server, then after creating this accounts and gave it out to them. They can access based on different scenarios. Lets say, one person is going to come into the server and write some stored procedures. That person can log on to the server using for example, his personal laptop with internet access that has SQL Server Management Studio and connect to the 2smart4school server. There is an ip address for all computers on the internet and the person can simply put the ip address of the 2smart4school server machine into the server name option and select the type of authentication to use and provide username and password. To read more on how to connect remote and the connection strings, go here http://www.connectionstrings.com/sql-server
Ok, so what have we done, we defined logins that can be used to access the server, the process of verifying that a particular login is valid is called authentication. After creating a login to access the server, this person has to be given roles to play or restrictions on their account. Login into the server doesn’t not mean the person have access to everything. The account has to be mapped to actions they can perform and the databases they have access to. At the server level, the account is given server roles which are made up of bulkadmin, dbcreator, diskadmin, processadmin, public, securityadmin, serveradmin, setupadmin and sysadmin. By default, every new login has public server role. To specify additional roles, you can use the SQL server management studio or TSQL and since we love TSQL so much, l’m going to demonstrate how to give a server role of dbcreator to our manager account. Which simply means the manager can create a database.
USE Master
GO
EXEC master..sp_addsrvrolemember @loginame = N’manager’, @rolename = N’dbcreator’
That was easy. I like system stored procedures. They make life easy.
Now, have created this manager login and we say, yeah, they can create databases on the server. What about access to databases they do not create? That’s another trick to worry about. When you create a login account, you to also create a database user for each database the account can access. You can do this using management studio by expanding the specific database, go to the security section in the database, expand it and right on User folder to create a new user. In creating the new user, you have to map the login by selecting the login name you’ve created at the server name. Then specify the roles this user have on the specific database. Lot of roles to define so l’ll leave that out here. But don’t forget, we are TSQL loves, so lets do the same thing using TSQL. One important thing, the database user has to be stored in the specific database you are creating the user to access. Logins are stored in Master database but the database user account is stored in the specific database the user is to access. Ok, let’s create a user for adventureworks database and map the manager login account to it.
USE [AdventureWorks]
GO
CREATE USER [aw_user] FOR LOGIN [manager]
GO
Yes ! Yes!, we did it. That was very hard. Easy money guys. Its simple logic. we created the user and map it by using FOR LOGIN and the login name.
Now that l have the database user mapped to manager. l want to give the roles that this database user can perform in my adventureworks database. Once again, it’s easy to do this in management studio with the nice graphical interface but since we really love TSQL, we are going to write the code for it.
USE [AdventureWorks]
GO
EXEC sp_addrolemember N’db_accessadmin’, N’manager’
GO
wow, that was fast. There are different roles to give to the user but here, we gave the manager db_accessadmin. Members of the db_accessadmin fixed database role can add or remove access for Windows logins, Windows groups, and SQL Server logins. I can’t go over all the database roles and server roles in this post so l’ll leave that for research. Once again, a database user account is used for database access. A login must be associated to a sql server database user. The user account is used to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to the sql server. So, a login must be mapped to users for each database one needs access to.First we create a login and then map users for that login.
Another important thing l want to talk about before l end this whole crap is DCL. DCL = Data Control Language. We have few of them, they GRANT, DENY, REVOKE, GRANT WITH GRANT. DCL is a set of clauses through which DBA can control the access to different database objects. More of granting access to user according to their role organization. DCL is used to grant and deny DML access to database objects, NOT DDL.
Syntax is like this
DENY/GRANT/REVOKE SELECT/UPDATE/DELETE/INSERT
ON [object name] TO [Database User Name]
For example, lets say we don’t want the manager to delete anything in the employee table in the adventureworks database
DENY DELETE
ON Adventureworks.humanresources.employee TO aw_user
IF we want to give them that role,
GRANT INSERT,DELETE,UPDATE
ON Adventureworks.humanresources.employee TO aw_user
Finally, SQL SERVER AGENT is part of the server used for automating stuff. It is a tool within the SQL server, that allows for automating process and running them at a scheduled time. You have to turn it on if its not running before creating any job. This is incredibly long, l didn’t think l was going to spit out so much lol. Hopefully, this will save someone.
Questions, suggestions, corrections are welcome. Peace. I’m out.
