Wednesday, January 21, 2009

MOM 2005 & SCCM 2007

We are testing MOM 2005 at this time and hopefully will move to SCCM in the following months, the MOM 2005 server had been in place for a long time but haven’t been properly configured.

It took me almost 2 days to go through the documentation and eventually got it sort of working.

I have to say through, as part of Microsoft’s MOF model and closely correlated to ITIL, MOM’s use in daily operation will definitely be more and more important.

 


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

Tuesday, January 20, 2009

SQLSafe -Arithmetic overflow error converting numeric to data type numeric

SQLSafe -Arithmetic overflow error converting numeric to data type numeric

While I have been using SQLSafe for database backup for a while, today there is a new issue turned up.

When I tried to access the console, one of the policy showed the above error message and can’t display relevant job history.

I then removed the policy, re-created it but still no luck to get it back.

After doing some investigation, finally I found out this was caused by the fact that one of our database size is so big that one stored procedure of SQLSafe actually can’t handle it.

The stored procedure name is “ms_get_server_status” and the fix is to look at line 81 and look for numeric(10,2) and change it to numeric(20,2).

After made the change, sqlsafe console is finally back

Monday, January 12, 2009

Ownership Chaining

Ownership chaining is the classical way of giving users access to objects through stored procedures in SQL Server. And while SQL Server provides two new methods, ownership chaining is what you will use 95% of the time. Certificates and impersonation is something you only have reason to use when ownership chaining does not do the job.

How does ownership chaining work? Say that you have a procedure sp1 owned by user A. sp1 performs a SELECT from tbl1 and tbl2. tbl1 is owned by A, whereas tbl2 is owned B. User C has permission to execute sp1. To be able run this procedure successfully, C needs SELECT permission on tbl2 but not on tbl1. This is because sp1 and tbl1 have the same owner, and this is ownership chaining. Ownership chaining can also be achieved through triggers, user-defined functions and views.

Now, this may seem a little complex to grasp, but in real life it is often a lot simpler. In my experience, having several object owners in a database is not very common. In very many cases, dbo, the database owner, owns all objects in a database. A common way to implement security in a database application is to perform all access through stored procedures that validates input parameters, enforces business rules etc. When dbo owns all procedures and tables, users only need permissions to execute the stored procedures. Thanks to ownership chaining, they do not need any direct permissions on the tables.

The permissions on views also follow the same rules.


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________