Solved

SQL 2008 logins & schemas

Posted on 2010-11-08
10
622 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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:sqlservr
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 ?
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 show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

730 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