Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
The best practice for working with security in SQL Server is that you're using Windows authentication. That means before anybody actually accesses your SQL Server, they have had to log on to the domain or even log on to the local machine, as a legitimate Windows user, that user already exists. What that means is your job inside SQL Server is not declaring that there's a certain user called Barbara, certain user called Alice, but you're saying what is that user allowed to do in SQL Server.
And this is the way that we do it. Instead of opening up my Databases section, I am going to expand my Security and take a look right now at my Logins. Now, right now, I don't have a lot. In fact most of these are essentially background and service accounts because I haven't added anybody to access this instance yet. So I am going to right click the Logins folder and say New Login. Let's be very clear about what we're doing. We are not actually creating a user here. The user already needs to exist outside SQL Server. We are just saying what are they allowed to do. Now, if I knew the name of the user, I could stop typing it in or I can use the Search button over here, even do a little search on the users that I have.
And I am going to use this user here called AmayaW. Now I have got a stand-alone machine, so I'm just using a local user, preferably in a production environment. You've got a domain user obviously. So that user is already defined. We are saying what is she allowed to do. And the two most important parts of adding this user as a new login is the Server Roles section, which says does that user have any special server role abilities? And the User Mapping section, which really means how is this user attached to or defined in any of the databases that we have that exist? So question one, should this user be granted anything particularly special? Should she be given sysadmin rights and be effectively the administrator for the entire SQL Server instance? Well, let's say in this case maybe not.
Though I could give her dbcreator and allow her to make new databases. By default, everybody gets put in the public role but the public role just doesn't get to do very much. So that's okay. I will leave it as it is. I'm going to jump across to the User Mapping section. What I want is for this new user to be added to the AdventureWorksLT database and the NorthwindSQL. If I just select the database, I can't change the role. I have to click this checkbox that says Map. Once I do that it's telling me I'm mapping this user to certain roles in that database.
So here's what I could give her db_ datareader and db_datawriter and leave her in public. I could come down a bit more and with NorthwindSQL perhaps just make her a reader. Click OK. That user has now been added to SQL Server and mapped to those two databases. Now of course if you had to do dozens or even hundreds of users, that's going to be a little tedious. So one of the things you can also do is when you're creating logins, if you have a group that you know of that already gathers the people together, it's being managed say in Active Directory, you can just use the name of the group.
I do have a simple group that's been defined here locally. It is called sales. Not showing up right now just because I'm only showing the built-in principles. Let's select Groups. Find again and there is the Sales group. Click OK, click OK. I am leaving the other options here. I don't need to map them to a certificate. I don't need to change the default database. But what I'm going to do is say no, they have no specific server role but in the User Mapping I'm going to map this and add everybody and give them the db_datareader privilege.
Click OK and that means from this point on, anybody who's defined on this machine is being in the Sales group will allow to be execute statements that read against that database. Now if Amaya was in the Sales group and given her own privileges, she's going to get the most, she is going to get the highest ones of whatever she has been granted. Even if I was defined in that Sales group, because I'm also defined as sysadmin in this instance I get to own everything everywhere. Adding groups of course makes it a lot easier to manage because you then actually manage your people into the groups in Active Directory.
You don't really have to worry too much about working with that in SQL Server. Now, just to show you what's actually happening when I map a user into a database is you will find that the databases I mapped to, such as AdventureWorksLT, themselves have their own security section with their own users defined. And when I selected to map them, we see the individual entries just for that database showing up here. I can right-click, select Properties, and see that in this case Amaya has datareader and datawriter.
I wouldn't get the choice here to add her to a server role but this is okay here, and again looking at Sales, I can see that Sales has just the db_datareader role membership.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training .
Here are the FAQs that matched your search "" :
Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.