[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2008 logins & schemas

Posted on 2010-11-08
10
Medium Priority
?
626 Views
Last Modified: 2012-05-10
Hi,

Can someone please better explain to me the picture below, I am looking at a security user on my newly created SQL Server, why do the users differ with relation to the default schema?

DatacollectorWH user is vand1\mirde and defautl schema dbo.
SALES_DW user is dbo, and default schema dbo.

Shouldn't the user be vand1\mirde and schema dbo?

What is the difference and any precautions I should take?
Untitled.jpg
0
Comment
Question by:mirde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Author Comment

by:mirde
ID: 34087626
I guess what I am trying to say is, shouldn't a user be assigned to the dbo default schema above? and not "dbo"? So that the user has access to the dbo schema.
0
 
LVL 15

Accepted Solution

by:
AmmarR earned 1000 total points
ID: 34087773
dear mirde

you need to understand the concept of users in SQL security model

in SQL you have LOGINs and USERs

a login is some one who has access to connect to SQL

a USER is when a login has access to a certain database

and the same login can have a different username fro different database.

so for example a LOGIN  vand1\mirde can be called as username vand in database DatacollectorWH

and can be called a different username in database Sales.

in this case.

i mean the in the picture you posted

the LOGIN  vand1\mirde is a user called  vand1\mirde under database DatacollectorWH
and its the dbo under database Sales_DW
0
 

Author Comment

by:mirde
ID: 34087891
Hi Ammar,

Thanks for your explanation, it is not quite clear to me, SQL Logins linked to different users on the database level, that can have different schema.

If I wanted to find out what SQL Login is linked to which users, is there a TSQL to do that? The reason for this is, if I want to remove a SQL Login, but before I do so link all the users that are associated with that Login to something else prior to removing it.
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
LVL 15

Expert Comment

by:AmmarR
ID: 34087994
no no

a SQL LOGIN is not linked to a different user.

i mean that a SQL LOGIN is a lets say a windows user.

now a sql login will only allow you to connect to sql, but you cant access any database.

now to allow this sql login to access a database you give him access to a database and give him a name.

so its the same login with different usernames for different databases, but if you delete the login, then all these alias names (usernames are all gona with it) and you need not worry about the links

in the image you posted it shows a login and how its mapped to each database and its username

if you want a script you can use this storeprocedure

sp_helplogins Loginname

look at this article

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

check the first section it explains what is a login and what is a user.



0
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34092066
I explained the user-schema relation using graphics in this blog:

http://sqlreality.com/blog/ms-sql-server-2008/user-schema-relation/
0
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 34092753
go to -> database--> security--> delete user then
server--> login-->create the login--> user map the login to the databases and rememnber to select the database roles
0
 

Author Comment

by:mirde
ID: 34096001
Thanks for the replies, now I better understand this.

Does it make better sense that if a single user is going to have dbowner permission on a database, to assign that user to the "dbo" user on the database.

The dbo user is by default owner of the dbo scheme as well, in the end this to me makes sense.

Just wanted some feedback for the above.
0
 

Author Comment

by:mirde
ID: 34096022
Just to clarify,

If my domain user "user123" is the dbowner of SALES database, then assigning his SQL Login to the dbo user on the database, which is the default owner of the dbo scheme should be ok?

Any reason not to assign the user to the dbo database user, but rather and adding the SQL login to the database users instead.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34099447
for first question yeah its ok

second question, if hes the db owner its just fine, but if you dont want him to be the db owner and want to give him less permission then no need to do so
0
 

Author Closing Comment

by:mirde
ID: 34141774
This answered my question, once I transferred to my new SQL server I tuned up the login security.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question