Confused regarding user tables in MS-SQL

KAMAZON

Golden Member
Apr 4, 2001
1,300
0
76
www.alirazeghi.com
I've been going through learning SQL and one thing I'm confused on is the fact that users can get their own tables. In one of my tutorial videos the guy briefly mentions how the chain of owndership works, and how SQL will first look for a table with that users name first, before the DBO table.

Is Mike.Employees a table that Mike can grant other people access to? Is a DBA the only person who can grant access to Mike.Employees?
 

KB

Diamond Member
Nov 8, 1999
5,406
389
126
An administrator can grant Mike the "Create Table" right. Since Mike is not the dbo_owner, when he creates a table it will be created as Mike.TableName.

Any table that Mike has the CONTROL permission on, he can then control who can SELECT, INSERT, UPDATE on that table. I believe if Mike creates the Table he gets CONTROL permission on that table by default.



Also, never use User created tables. Always have the creator be dbo or take ownership of the table using dbo. It just makes life easier.
 

brandonb

Diamond Member
Oct 17, 2006
3,731
2
0
I haven't ever done it or played with user tables, but if I had to guess how it worked:

The idea is scope.

Let's say you have the user MIKE, which has a user table Employees. There is also a dbo Employees's table.

You can write a SQL statement like:

SELECT * FROM mike.employees.
SELECT * FROM dbo.employees

Those will query from the correct area.

However, what if the statement is :

SELECT * FROM employees

Which one does it read from?

If the user is MIKE, it will read mike.employees. If the user is anybody other than MIKE, it will read from dbo.employees.

Can other people access mike.employees? I believe they can provided they have permission which is setup by the DBA. But they need to access the user table directly using the mike.employee in the SQL statement. Even with permission if they don't specify mike, it will read from the dbo anyways.