Solved

SQL 2008 logins & schemas

Posted on 2010-11-08
10
618 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
10 Comments
 

Author Comment

by:mirde
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 15

Expert Comment

by:AmmarR
Comment Utility
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
Comment Utility
I explained the user-schema relation using graphics in this blog:

http://sqlreality.com/blog/ms-sql-server-2008/user-schema-relation/
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 10

Expert Comment

by:sqlservr
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This answered my question, once I transferred to my new SQL server I tuned up the login security.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A procedure for exporting installed hotfix details of remote computers using powershell
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now