Solved

SQL 2008 logins & schemas

Posted on 2010-11-08
10
620 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

776 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