SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LoginID, User, UserGroup, Schema and Role.


LoginID, User, UserGroup, Schema and Role.

Author
Message
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 1255
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.

--
MW-309385
MW-309385
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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.
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 1255
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.

--
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 1255
can anybody help me for this concepts??

Cheers!

Sandy.

--
Van Heghe Eddy
Van Heghe Eddy
SSC Eights!
SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)

Group: General Forum Members
Points: 873 Visits: 900
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
MW-309385
MW-309385
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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

)
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 1255
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..

--
MW-309385
MW-309385
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 1255
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.

--
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search