Solved

SQL 2008 logins & schemas

Posted on 2010-11-08
10
623 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 250 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 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