Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LoginID, User, UserGroup, Schema and Role. Expand / Collapse
Author
Message
Posted Wednesday, June 25, 2008 3:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
Hi All,

Today I had a session on Sql Server Security.
But I am really confused that, how these all are correlated.

LoginID: Login to SQl Session.
User: Database User or Windows User (Confused)
UserGroup: It’s not possible in SQL and Possible in Windows.
Role: For Permission Assignment to UserGroup.
Schema: Its owner of database.


I have right now this much of Knowledge on the above topics.

What I need is, please tell me the Definition, Difference and uses of these things.
And also I want to know how they are correlated to each other.

I put this question to clear my concept on Sql server security part.
Please help me for this.

Cheers!
Sandy.


--
Post #523105
Posted Wednesday, June 25, 2008 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:37 AM
Points: 27, Visits: 45
A login ID is also known as a principal in SQlsever.
loginID = Similar to a ID that you would use to enter a building.

There are 2 types.
1.) logins created using mixed authentication
2.) logins created using the WINDOWS user ID

Lets talk about #1
Each new user who wants to do anything with SQl server will be given
a LoginID and a password to enter the database.

Now #2 - Similar to #1 except for the fact that SQL server will use
your WindowsID and password to enter you in to the database.

Usually when using #2 users will enter a user id that has 2 parts.
It will look like Andromeda\Brian_Hippo

Here, the name Andromeda is the domain name and Brian_Hippo is the actual userid used to login to the WINDOWS network.


I will leave the rest for someone else to answer. I could! But not the best answer.





Post #523277
Posted Thursday, June 26, 2008 1:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
Hi MW,

I am impressed on your answer.

But can you please tell me one more thing,
As per you "Andromeda\Brian_Hippo" is windows user, But if you navigate the Database in Enterprise Manager, you'll find a user tab on each database. and it allows you to create a new user on this.

Then is this same with windows user or not? If not then what is the difference between the windows user and database user concepts here?

Also need clarification on the rest topics too.
Can anyone clear me on this...

Cheers!

Sandy.


--
Post #523911
Posted Thursday, June 26, 2008 10:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
can anybody help me for this concepts??

Cheers!

Sandy.


--
Post #524760
Posted Friday, June 27, 2008 4:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 4:56 AM
Points: 592, Visits: 753
Sandy,

You can see users idd on each database where they have rights to,
You wil see the SQLServer users and the WindowsUser if any,
Or you can even see Windows User Groups on these tabs, and SQLServer Groups

The great difference:
SQl server uses userlogins on Server Based niveau (witch tells whether you may access the server),

Then once a user has access to a server this does not mean he also have access to different databases / tables /views or any, they have to be declared .

The easiest way for management purposes is to create windows user groups in your Active Directorie and use these in your SQL Server environment,
This way you can declare access to a specific database / table for a certain "usergroup", otherwise you end up setting individual rights for every user.

Ps: you can also put a windows user/usergroup in a SQLServer group (db_datareader,..)

Schema's is not my thing but i try, please correct me when im wrong.
Schema's are something where you can divide ownership in a certain database,
take for example a database called "TEST", when created by the sa this would mean that every table created in in database TEST would be prefixed by dbo.tablename
so you get in full syntax:
SQLSrvname.Test.dbo.tablename
but here you can create schema's on serverniveau like ex. schema "finance"
put the user "jan" as schema owner for this schema

now you could create a table like
SQLSrvName.TEST.Finance.janstable in witch the user jan would be the owner

i hope this helps you further.
wkr,
Eddy
Post #524867
Posted Friday, June 27, 2008 7:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:37 AM
Points: 27, Visits: 45
Sorry for not getting back......
Let's back up here and answer your first question

---You said -------
Hi MW,

I am impressed on your answer.

But can you please tell me one more thing,
As per you "Andromeda\Brian_Hippo" is windows user, But if you navigate the Database in Enterprise Manager, you'll find a user tab on each database. and it allows you to create a new user on this.

Then is this same with windows user or not? If not then what is the difference between the windows user and database user concepts here?

Also need clarification on the rest topics too.
Can anyone clear me on this...

Cheers!

Sandy.

-------MY REPLY follows ------
Earlier I said the login is like a ID card to enter a building.

Now just because you have an ID and enter a building, that ID does not allow you to enter every room.

Inside SQl server you have databases ( one or more ).
In ordder to visit ( or use ) these databases, your login has to have
a matching user.

What does that mean ? For each login ( like Andromeda\Brian_Hippo )
you have to create a user that goes with it.

So it's like a pair ( husband and wife ).
The following syntax will explain this properly.

CREATE USER brian_hippo FOR LOGIN "Andromeda\Brian_Hippo"

Now, that is the user that you see when you user SS.Management Studio

That's for now..

Let me explain one more topic.
What is a SCHEMA ? A schema is a logical namespace.

What does that mean ?
A schema is like a folder in windows. Allows you to organize your objects together.

( I have folders in my filing cabinet to store different types of bills.
So then , any drawer in my filing cabinet is like a database and each folder inside the drawer is a schema)

The following syntax will create a schema.
CREATE SCHEMA Shipping

To create a table inside the schema you must use the schema name infront of the object.

Ex:

USE Mydatabase; --I want to use the database that has the schema
Create table Shipping.orders
(
Name

)




































































Post #525005
Posted Friday, June 27, 2008 9:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
Hi eddy, Thanks man...
Can you clear me on this line...

Ps: you can also put a windows user/usergroup in a SQLServer group (db_datareader,..)



MW, Excellent...
Very clear on this...a real time example...although i am single but i can understand..

So it's like a pair ( husband and wife ).
The following syntax will explain this properly.

CREATE USER brian_hippo FOR LOGIN "Andromeda\Brian_Hippo"


you said...

Let me explain one more topic.
What is a SCHEMA ? A schema is a logical namespace.

What does that mean ?
A schema is like a folder in windows. Allows you to organize your objects together.



Can you clear me how schema owns the security level...
I mean to say how it works for security level point of view..

Is there any relationship between schema and users....
And more thing Can Role can be assign user level or not....?


Cheers!

Sandy..


--
Post #525115
Posted Friday, June 27, 2008 10:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:37 AM
Points: 27, Visits: 45
RE: SCHEMA
You are right. One of the main advantages of having a schema is for security reasons.

There are different rights that you can grant on objects to each user.
That is a lot of work to keep track of who can do what in the database.
An easy way to handle this is to put some of the tables and other objects
inside a schema and then grant rights on the schema ( and the things inside the schema will automatically receive the same rights )

Example: Lets say you want to donate certain items in your home.
Instead of having to put labels in each of them saying that it is free,
just put all of them inside a box and have one big label that says
"Free". So the big box is the schema.
Without this big box you would have to do a lot of work having to
label each item as "Free".

Makes sense ?


The following command gives exclusive rights to Danny to
visit the schema. Just one sentence and you don't have to bother
what Danny can do with the objects inside the schema. Why? Because you already gave the righst to the "SCHEMA" when you created it ( assuming you did ).

ALTER USER Danny WITH DEFAULT_SCHEMA=HumanResources






Post #525162
Posted Friday, June 27, 2008 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
Hi MW,

Example: Lets say you want to donate certain items in your home.
Instead of having to put labels in each of them saying that it is free,
just put all of them inside a box and have one big label that says
"Free". So the big box is the schema.
Without this big box you would have to do a lot of work having to
label each item as "Free".

Makes sense ?


Yup, good....let me do some work (RND) on this..if I find any difficulty I will again come...
thanks man a lot...

Cheers!

Sandy.



--
Post #525237
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse