what is different between User & Login

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what is different between User & Login

Author

Topic

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-22 : 22:07:54

what is different between User & Login ?
and how can I use them ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-22 : 22:33:50

see here
http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/04/21/sql-server-security-basics-logins-vs-users.aspx

KH
[spoiler]Time is always against us[/spoiler]

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-23 : 08:59:26

not clear !

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-23 : 09:35:28

A login grants you access to a SQL Server. To paraphrase the example given, think of a login as the key to a house.

A user grants you access to a database. This is analogous to a key to a lockbox, or file cabinet, or some smaller item inside the house that needs a key.

So a login grants you access to the server (house), but doesn't necessarily grant you access to any databases (file cabinets), which require their own keys. (If you have teenage daughters or sisters who keep a diary you can be sure they don't want anyone else reading them)

By the same token, a user may have a key to a file cabinet (database), but you may not want them in your house (server), so you don't give them a house key (login).

So to access a database in SQL Server, you need BOTH a login and user set up to grant access. Furthermore, the user must be based on an existing server login. This is 2-factor security, which ensures only valid logins can access the server, and ensuring only valid users can access their respective databases.

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-23 : 10:14:08

i never create a user , only I create 1 login and can do all operations in sql database such as :insert,delete update,without creating an user.
so what is the benefit of an user.
I need an example using both :user & login.
thank you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-23 : 10:31:27

I can't explain it any better than what's already been presented. If you're using a single login like sa (or another administrator-level login) to do all your database access, you're working in an insecure way.

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-23 : 10:57:13

yes exactly i use sa, m_admin as login to do all operations in database
and I have 50 employees in that company I work for. they are logining using m_admin & password:1000105
and so on, what is the best to give all users a permitting only on (DML)like:select ,insert,delete, update only.
thank you very much.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-23 : 11:03:03

You just posted a sysadmin username and password to a public forum???? Wow, that's a security stuff-up.

Security recommended practices are to give each person who needs access their own login and grant that login the minimum permissions needed to do their work.

--
Gail Shaw
SQL Server MVP

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-23 : 11:21:03

please help me to give a security using login & user

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-23 : 11:58:52

Please open up Books Online (the SQL help file) and do some reading. It's a large topic, I can't tell you everything.

If you have specific questions once you've done some reading, feel free to ask.

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-23 : 12:15:22

I have no hope that I will succeed where both Rob and Gail have failed, but fear of failure has never stopped me before, so I am gonna try.

Do an experiment. I am using SQL 2008. I think SQL 2005 would work pretty much the same.

1: Open up SSMS, object exporer, navigate down to YourServer->Security->Logins.

2. Right-click on Logins and select "New Login". A window will pop-up.

3. Type in SunitaBeck in the Login Name box

4. Now click the User Mapping node on the left panel in that window.

5. In "Users Mapped to this Login", click one or two databases.

6. Now, don't click OK, instead, click the Script icon at the top towards the left of the window.

This will create a script for the actions you were about to do and show you. Take a look at it. It would be something like this:

USE [master]
GO
CREATE LOGIN [Sunita] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [MyDB1]
GO
CREATE USER [Sunita] FOR LOGIN [Sunita]
GO

The first part is creating the login, and the second part is creating a user for that login on the database(s) that you clicked.

Does that make sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources