| Author |
Messages |
|
John Kelley
 |
| 03/01/2002 5:09 PM |
 Quote
 Reply
 Alert
|
| I am registered with TechNet and received weekly tips about SQL. The last tip caught my interest and I never quite understood the meaning...TIP:
Set database objects to system objects if you dont want SQL Server users to see these objects. For example, you may have security tables that assist in setting up database security, which you dont want users to view. A practical implementation is to set as system objects the stored procedures, tables, views, etc., used in applying read, insert, update, delete, and execute permissions to other user database objects.
It then goes on to explain how to do so. I dont see the advantage of doing this...If permissions are set properly, why bother with this? Would it be an added layer of security...??? Comments? |
|
|
|
|
Chip Andrews
|
| 03/04/2002 9:33 AM |
Quote
Reply
Alert
|
| IS there a KB article or other reference for the document so that I can see what you are referring to? It sounds like they are suggesting that you not give users direct access to certain tables and instead provide controlled access only through stored procs and views. That approach makes good sense but all this talk of system tables is dung. |
|
|
|
|
John Kelley
|
| 03/05/2002 10:43 AM |
Quote
Reply
Alert
|
| Here is the entire article that I received in the email tip:
WHEN SHOULD DATABASE OBJECTS BE SET TO SYSTEM OBJECTS?
Editor's Note: TechRepublic is preparing for the March 4 launch of our
sister site, Builder.com. As part of this exciting new product, you will
notice that your next SQL Server e-newsletter will carry the Builder.com
brand.
Builder.com is a new resource for a new kind of developer. We recognize
that top developers do more than just write code. To be successful today,
developers need to deliver the technological innovations and ideas that
drive business. Builder.com and its e-newsletters will be dedicated to
uncovering those ideas and innovations.
Set database objects to system objects if you don't want SQL Server
users to see these objects. For example, you may have security tables that
assist in setting up database security, which you don't want users to view.
A practical implementation is to set as system objects the stored
procedures, tables, views, etc., used in applying read, insert, update, delete,
and execute permissions to other user database objects.
The procedure used to set database objects to system objects is
sp_MS_marksystemobject. It takes one parameter, the object name within the user
database, and resides in the master database. You can call it from any
user database. It marks the user database object as a system object. For
instance, you can mark a user-defined database table within a database as a
system object. If you use the function OBJECTPROPERTY with the property
parameter of 'IsSystemTable' on the user-defined database table, it will
return '0', indicating False (that it is not a system table). However, if
you expand the server group in SQL Enterprise Manager (SEM), expand a
server, expand the database folder, expand a database, and select the
tables, it will display the user-defined database table as a system table. In
other words, SEM will see the user-defined database table as a system
table; however, when the function OBJECTPROPERTY is used to identify the
table as a system table, it (the function) will not recognize the table as a
system table.
|
|
|
|
|
|