LoginID, User, UserGroup, Schema and Role.

Question

Post reply

LoginID, User, UserGroup, Schema and Role.

IN_Sandeep

SSCertifiable

Points: 6259
More actions
June 25, 2008 at 3:16 am

#188415

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.
--

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MW-309385 Old Hand Points: 341 More actions · Answer 1

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 SSCertifiable Points: 6259 More actions · Answer 2

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 SSCertifiable Points: 6259 More actions · Answer 3

can anybody help me for this concepts??

Cheers!

Sandy.

--

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 4

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 Old Hand Points: 341 More actions · Answer 5

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 SSCertifiable Points: 6259 More actions · Answer 6

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 Old Hand Points: 341 More actions · Answer 7

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 SSCertifiable Points: 6259 More actions · Answer 8

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.

--