Monday, August 30, 2010

Limit Sql data access from Applications using "EXECUTE AS"

When you have to retrieve SQl data from the applications, you would create login and connect using that to the sql server. Any user, who has the permissions to execute the stored procedure, runs the stored procedure under the Database's dbo user (which means it can do anything in the database, but nothing at the server-level nor on other databases). If you only allow your Logins to execute stored procedures (and not touch the tables directly), then you've effectively limited the Logins to code you've written. If you don't write any DELETE statements, then Logins can't delete anything.


With Sql 2005 and above, there is a new feature EXECUTE AS OWNER which is a great way to limit the permissions of a sql server login. Lets look at how this can be used effectively to limit the access.


This feature allows you to impersonate another user in order to validate the necessary permissions that are required to execute the code without having to grant all of the necessary rights to all the underlying objects and commands.
The EXECUTE AS clause can be added to stored procedures, functions, DML triggers, DDL triggers, queues as well as a standalone clause to change the user’s context. 


Syntax:
CREATE PROCEDURE.[proc_GetAliasByID]
      @ID bigint = 0
WITH EXECUTE  AS 'AppDMLUser'

Thursday, August 26, 2010

SQL Change Tracking Vs CDC

I had posted about SQL change tracking couple of weeks ago. There is one more option available on SQL for capturing the data and can be used for auditing. That is called as CDC – Change Data Capture


Here is the comparison on these two features

Change Tracking (CT)

Change Tracking is a synchronous mechanism which modifies change tracking tables as part of ongoing transactions to indicate when a row has been changed. It does not record past or intermediate versions of the data itself, only that a change has occurred

Change Data Capture (CDC)

Change Data Capture is asynchronous and uses the transaction log in a manner similar to replication. Past values of data are maintained and are made available in change tables by a capture process, managed by the SQL Agent, which regularly scans the T-Log. As with replication, this can prevent re-use of parts of the log. This Tracks when data has changed and includes the values as well. Entire table or subset of columns can be captured.

Tuesday, August 17, 2010

Universal Data Link (UDL) files

If you have worked with VB or ADODB objects, I am sure you will have used this earlier. Even though we have many ways to keep the connection strings in configuration I still use UDL many times even now.  Keeping connectionstring on configuration is a common requirement but to get that string we can use UDL that  keeps it simple to get the connectionstring right away from the notepad without worrying about format etc.

Here is how I normally do to get the connection string or to test the connection.
·         Open a note pad
·         Save it as “x.udl”
·         Close the notepad
·         Now open the UDL file
·         Configure your connection and save it
·         Open the UDL file in note pad again. Now you get the connectionstring

Simple way to check the connection to servers and helps to debug the connection issues

Sunday, August 15, 2010

TFS 2010 for agile scrum development

We have started using TFS 2010 for our scrum :-) . I found TFS 2010 has lot of features to work with scrum agile methodology. It has capabilities to create user stories, product backlogs, and sprints etc. This can also be used for daily scrum updates.


The TFS team portal provides access to users to update the tasks and many reporting capabilities.

Here is one of the nice ppt I found Scrum with TFS 2010 which has detailed explanation how we can make use of TFS for scrum methodology. http://www.slideshare.net/aaronbjork/scrum-with-tfs-2010

 
 
Happy reading!! :-)