Trying to understand security - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Trying to understand security

Author

Topic

dirtdevil
Starting Member

4 Posts

Posted - 2012-01-18 : 10:49:43

I'm new to sql server and I'm trying to understand the different aspects to security. Specifically how users,logins,schema,databases,roles and principals relate to each other. I know a user is a windows user that you can map to a login which is a sql user. I know what a database is but I thought a database was a schema? I don't understand how a principal or a role fit into all this. Is there a tutorial out there that explains how sql server security works in a simple way? I've already read up on it in 2 different books and still don't get it.

I come from a mysql oracle background if that helps any.

chris_cs
Posting Yak Master

223 Posts

Posted - 2012-01-18 : 11:30:44

Have you looked at Books online (BOL) for information on Schemas etc?

There is plenty in there

----------------------------
Junior DBA learning the ropes

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 12:31:32

In other words, a schema is simply a container of objects

http://msdn.microsoft.com/en-us/library/ms190387.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dirtdevil
Starting Member

4 Posts

Posted - 2012-01-18 : 13:01:15

wow, that top link really helped. that cleared things up about schemas. My followup question is, if schemas can have objects in it, why don't schemas appear as folders in SQL manager like databases do? Also, if schemas hold database tables, then what purpose do databases have?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 13:26:49

they appear in the object folders they belong in, and are just qualified by the schema.object

And my 2 cents

I have NEVER seen a reason to use a schema other than dbo

If data is coming from different sources..I would create separate databases

That increases maintenance, but I could live with that over the nightmares schemas could inflict

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dirtdevil
Starting Member

4 Posts

Posted - 2012-01-18 : 14:47:40

Oh. I think I get it. So for example. If I wanted to access the students table under the school schema, I would just do a ...

SELECT * FROM school.students;

But if the students table was in the enrollment database, could I still get to the student table via....

SELECT * FROM enrollment.students;

If so, then your right. I don't really see a need for schemas.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 15:07:02

No, if Students was in the Enrollment database, under the school schema, you'd get to is via

SELECT <column list> FROM Enrollment.School.Students

Database.Schema.Table
or
Schema.Table (for tables in the current DB)

--
Gail Shaw
SQL Server MVP

dirtdevil
Starting Member

4 Posts

Posted - 2012-01-18 : 15:27:19

Ah ok. That makes sense. A few more questions...

1. Do I have to have schemas at all? I know there's a default schema. For simplicity sake, can I delete that and just access all my objects via the databases they are in?

2. What are principals and can you give some examples? The definition in one of my books was "Principals are entities that can request SQL Server resources" But that statement is so watered down, it could mean anything as an "entity". I know it talks about windows level, sql server level and database level principals and principals are users and principals can form a hierarchy. But it's still kind of a mesh of info.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 15:31:38

No, you have to have a schema, but if you don't want to worry about multiple schemas (they do have uses), put everything in the dbo schema. It's not database or schema,it's a hierarchy. Databases have one or more schemas, which contain 0 or more objects.

Principals - at the server level that's a login, at the database level that's a database user. In a nutshell, it is more complex

--
Gail Shaw
SQL Server MVP

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-01-18 : 15:40:05

You cannot delete the default schema.

You can reference database objects without the schema but SQL Server applies specific rules to locate the object that can result in unexpected behaviour if you don't understand it.

In practice, most applications put all objects in the dbo schema.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 16:14:44

quote:
Originally posted by Michael Valentine Jones

You cannot delete the default schema.

You can reference database objects without the schema but SQL Server applies specific rules to locate the object that can result in unexpected behaviour if you don't understand it.

In practice, most applications put all objects in the dbo schema.

CODO ERGO SUM

MOST...I've seen some rocket scientists..use it...just because

And what those "Specific" rules are...I have no idea, no desire to know

For example

You have

dbo.Students
enroll.Students
Report.Students

SELECT * FROM Students?

I'm guessing dbo wins

I would have 3 databases in this instance

Then, I would have a VIEW that would apply the Business Rules as to which Student Data "Wins"...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 16:27:29

quote:
Originally posted by X002548

For example

You have

dbo.Students
enroll.Students
Report.Students

SELECT * FROM Students?

I'm guessing dbo wins

Depends what the user's default schema is. If it's enroll, then that resolves to enroll.Students, if the user's default schema is Report, that defaults to Report.Students. If the user's default schema is anything else, it defaults to dbo.Students

But that's a bad example to be setting. An unqualified table is frankly lazy coding. Qualify tables with their schema, even if they're in dbo.(but then SELECT * is lazy coding too)

SELECT <column list> FROM dbo.Students;

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 16:31:03

OF Course..although I usually don't qualify tables as [dbo].[table]

Since I make sure we always ONLY have dbo

I HAVE had to change that when I've inherited stuff however

So, do you say that you should always use 3 part names?

[dbname].[schema].[table]

????

I've seen people coding like that as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 17:12:38

No, not unless you're doing cross-database queries and aren't sure what database context the query will run from. Qualifying the tables however is a well-documented recommendation, has to do with resolution of object and plan reusability. That goes for dbo.<table name> as well.

--
Gail Shaw
SQL Server MVP

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-01-18 : 17:42:06

To add to what Gail stated - not schema qualifying objects can cause multiple plans to be generated for the same code. If that code is accessed by multiple users and each user has their own default schema (default setup - btw), then you end up with a plan for each user.

In other words:

UserA with default schema UserA and UserB with default schema UserB, the query 'Select {column list} From MyTable;' will have 2 plans in cache, one for UserA and one for UserB.

Changing the default schema for each user to dbo - or modifying the query so it is schema qualified for all objects will then have a single plan generated only.

Jeff

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 17:54:42

quote:
Originally posted by jeffw8713

UserA with default schema UserA and UserB with default schema UserB, the query 'Select {column list} From MyTable;' will have 2 plans in cache, one for UserA and one for UserB.

And that's even if all objects are in the dbo schema

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources