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 12»»

I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance Expand / Collapse
Author
Message
Posted Wednesday, January 7, 2009 5:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 4:25 AM
Points: 14, Visits: 288
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance
Post #631354
Posted Wednesday, January 7, 2009 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 13,884, Visits: 28,278
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance


Have you looked them up on the Books Online? If so, what did you not understand from there?

Here's a bit to get you started:

Login = Server
User = Database
Schema = Object Owner
Role = Security aggregation.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #631391
Posted Wednesday, January 7, 2009 6:23 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 4,427, Visits: 4,175
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


Markus Bohse
Post #631395
Posted Thursday, January 8, 2009 12:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 7, 2011 8:25 AM
Points: 60, Visits: 80
Theses are concepts that are good starting points if you are new to SQL Server, and reading Books Online is a good place to start also. It is best to read about these concepts first, and then ask more specific questions, that way the more experienced DBAs will feel like you have an interest in the topic and are willing to research as appropriate.

Roles: I think of creating roles in the database to group users of like function. Roles are granted certain permissions in the database. You should become familiar with the fixed database roles since these will be utilized once you start creating users within the database. Also, once you see the type of permissions that are granted to each role, is makes more sense.

Schema: there can be several schemas in a database, which will house different types of objects such as tables, indexes, stored procedures, functions, etc. Users own schemas. Looking into the AdventureWorks database illustrates this concept, with several schemas like HR, Production, etc.

Login: Think about login as gaining access to the SQL Server instance. If a user account is not granted any permissions within the instance, you basically just were able to unlock the door and enter the room, by creating a user you then grant access to the database objects or principals, and can begin to work with them.

Users: Users own schemas, and as such will be able to manipulate the objects they own. Some of the manunipulations are very permissive, such as creating tables, indexes, stored procedures, functions, etc. These are developers and administrators.

Users are created and granted permissions for application use, which will have select, update, insert, and delete and execute permissions to a finite set of objects in the schema, for which the application will need to function properly.

In a client server database, as an example, of the structure. Roles were defined which provides the permissions to the database objects in the database, which only has one schema 'dbo'. One SQL server login was created with the same username, and dbo is the assigned default schema, and the roles assigned to that username.
In the application, each specific user is given there own "application" login which is mapped to the one defined sql server login.

Hope this helps with understanding some of these concepts.
Good luck with your studying.

Jennifer


Post #632772
Posted Thursday, March 27, 2014 9:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:01 AM
Points: 54, Visits: 151
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!
Post #1555477
Posted Thursday, March 27, 2014 9:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
robin.pryor (3/27/2014)
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!


While I agree that the comment was a bit harsh, this thread is over 5 years old.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1555501
Posted Thursday, March 27, 2014 9:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:01 AM
Points: 54, Visits: 151
Sean Lange (3/27/2014)
robin.pryor (3/27/2014)
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!


While I agree that the comment was a bit harsh, this thread is over 5 years old.


But the point is valid and if it reaches even 1 person, which it did, I feel better.
Post #1555521
Posted Thursday, March 27, 2014 10:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 13,884, Visits: 28,278
robin.pryor (3/27/2014)
Sean Lange (3/27/2014)
robin.pryor (3/27/2014)
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!


While I agree that the comment was a bit harsh, this thread is over 5 years old.


But the point is valid and if it reaches even 1 person, which it did, I feel better.


And I feel good that I was helpful 5 years ago.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555532
Posted Thursday, March 27, 2014 10:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:01 AM
Points: 54, Visits: 151
Ah but the question "What landed me on this post in the 1st place?"

I'm a SQL DBA and I've never given a crap about schemas. I know what they are by definition, but have never once considered using them, because I'm probably too old-school. I think in terms of logins, groups, roles, and users. Now I'm in a circumstance where everybody and their mailman has had their hands in the cookie jar on this production server and nobody currently working here knows why things are the way they are. Should I create a new post to get flamed on "You call yourself a DBA....." or do you want to take a stab at answering me here?
Post #1555544
Posted Thursday, March 27, 2014 10:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:33 PM
Points: 3,771, Visits: 8,460
robin.pryor (3/27/2014)
Ah but the question "What landed me on this post in the 1st place?"

I'm a SQL DBA and I've never given a crap about schemas. I know what they are by definition, but have never once considered using them, because I'm probably too old-school. I think in terms of logins, groups, roles, and users. Now I'm in a circumstance where everybody and their mailman has had their hands in the cookie jar on this production server and nobody currently working here knows why things are the way they are. Should I create a new post to get flamed on "You call yourself a DBA....." or do you want to take a stab at answering me here?

The problem isn't that the OP didn't know, the problem is that this is clearly explained in BOL which should be the first option to look for answers for any SQL Server DBA. A question posted as this would have gotten different responses:
I've been studying about users, logins, roles and schemas, but I'm still confused. Can you help me understand the differences? I see that the user, login and schema are all the same, but the definitions are different.


The original questions seemed as: "Could you do my homework for me?"



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1555548
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse