August 8, 2008 at 3:38 pm
I am learning how to manage a SQL Server 2005 server and its databases.
I am puzzled as to why I cannot create two users in a database from the same Login.
There is some basic concept that I don’t understand. I’m a newbie at being a dba. (Been an app programmer for many years.)
Here’s an example from the MS SQL Server 2005 Implementation and Maintenance Book, Training Kit for the MCTS exam: 70-431. Page 85 contains some code in the Practice session, paragraph 1. that I enter and it works fine.
Here’s the code in case you do not have the book.
Create login Peter with password = ‘Pa$$wOrd’
Go
Use adventureworks
Go
Create user Peter from login Peter.
As I said, the above works fine.
When I experiment and attempt to add another user as shown on line 1 below, I get the error message shown on line 2 below. I don’t understand this and I don’t understand why I cannot create the second user. To my way of thinking I ought to be able to create many users all of whom use login Peter.
1.Create user Victor from login Peter
2.Msg 15063, Level 16, State1, Line 1. The login already has an account under a different user name.
Anyone: Please give me an explanation and end this confusion. The more verbose, the better.
Thanks.
Victor Victor
August 8, 2008 at 3:53 pm
Each user must be tied to their own login. Think of it as 2 seperate levels of security passthrough. A user must have a login set up at the SQL Server instance level to get it into the SQL Server. They must then be set up in the database to access that database. So all DB users have a login tied to them. Make sense? If you want to create a second user, create a second login.
August 8, 2008 at 3:59 pm
Thank you John.
I understand what you just wrote. I also accept it and will proceed with the idea that there must be at least one login per user, to use for the server itself, and another login, or database user login. Seems like needless duplication but if that's the way they got it set up, must be a good reason that will reveal itself to me later. THANKS.
Victor Victor.
August 8, 2008 at 4:05 pm
Well, just think of the case where you have multiple databases on a server. You may want to limit a login's access to which databases they can access. So you create a login for the person to access the SQL Server instance, then you create a user for that login in each of the databases that they need access to. They will then have access to the SQL instance and any database you've given them, but they will be unable to access any database where they have not been given access to because they are not a user in that DB.
August 8, 2008 at 4:06 pm
I'm wondering why you would want to create multiple users with the same login what keeps popping up in my mind is that you're trying to reduce the work required to administer users. "If these users have the same login then all I have to do is administer that login instead of each user."
If the above is true then I suggest you look at "Roles". These can reduce the work required to administer multiple users.
If the above is not true then I'd like to know why you want multiple users per login.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 9, 2008 at 2:43 pm
I'll reply to both John and Alvin here. Thanks both for the help/questions/suggestions. Once I read from John that a separate login is required for the sql server instance and then another for a single database on the server it became clear to me how I was confused. Remember that I am new at this. I've been writing selects, updates, etc, the programmer things for a long time, but have zero experience writing T-SQL create and alter statements.
Alvin hit the nail on the head about roles too. I was thinking that a login performed like a role. Having many individuals all in the same role is the ticket. So between the two of you, I'm straight (well at least temporarily).
I have put a separate post on this site, asking for people to refer to me a good text in which there are lots of problems and questions to test the completeness of the knowledge one needs as a working dba in the server, database, and security configurations. I want to have more than a cursory knowledge of working dba skill set not just enough to pass the exam. The only way I know to achieve that (without working at it in a job) is to practice, practice, practice.
So thanks guys.
Victor Victor
August 11, 2008 at 3:50 pm
Kayuca,
You might want to take a look at the resources that are available for certifications. These resources could provide a good list of the topics that you need to familiarize yourself with if you want to to call yourself "good". I'm currently doing something similar for database development.
I recently purchased the "MCITP Self-Paced Training Kit (Exam 70-441): Designing Database Solutions by Using Microsoft SQL Server(TM) 2005 (Self-Paced Training Kits)". I just started Chapter 3 and so far I can't say I'm impressed with the content of the book. However I would agree hat the book does cover most of the topics that someone needs be be familiar with to be good at developing database solutions. I'm using this book to help me figure out which areas I know well and which I need to learn more about.
You might be more interested in the database administrator topics.
This still does not answer you're question about what's the best books to get but it could help you figure out what topic to study.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2008 at 5:16 pm
Hi - forgive me if I'm missing the point of what you're trying to do, but here's my understanding;
Basically - what you're wanting to do is have a login tied to access to certain resources in the database or databases?
So if, say, Peter is a member of the finance team, you wish to be able to grant access to the other members with the same role in finance without the faff on of having to set up the permissions individually for each user every time?
If so, what you want to do is tie the permissions into the Windows / Active directory groups. So - you get your network team to set up a SQLFinance team - add the users to that group, and then set up that group with the appropriate permissions. Then whenever someone is added to the group they're automatically set up with the permissions you've set for it. It also means you have only one mechanism for handling things like new starters or leavers. When someone is added to a group, or removed if they leave, rather than have to be done in two places, it only has to be done in one
hth
Andrew
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
August 12, 2008 at 7:35 pm
Andrew:
Thanks for your comments.
Essentially John Rowan straightened me out a couple of days ago when he pointed out that there is a separate login for a server and once there, there is still more login for the particular database within that server for which a user needs access. That was my main point of confusion. I’m coming from a user (developer) background and ocassionally have difficulty assimilating the additional functions typical for dba-s.
I do have the Microsoft SQL Server 2005 Training Kit for the MCTS Exam 70-431. That text assumes one has a certain amount of dba experience under one’s belt which I do not. Nevertheless, I make up exercises for myself to cement home the concepts the book covers. Works, but is difficult and time consuming when I am presented with a totally new concept and have to struggle to integrate it into all the other material.
I dearly wish that Deitel and Deitel, publishers with whom I have learned much, had a book for dba trainees. I love the exercises at chapter ends. Working them most easily cements home the chapter contents. But there is no such book.
I have ordered a Wrox book for beginning dba-s and will consume it along with the MCTS Training Kit. A long road ahead but I’m between assignments anyway and have the time.
Regards,
Victor Victor
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply