Tuesday, June 14, 2011

Setting specific permissions on database objects for a DB user

From the days I am learning RDBMS ,I am hearing that its secured and we can set permissions at the DB object level.When I talk to my fellow DBAs they tell me that they can control so many things to stop hacking.They can restrict an application from executing queries and limit to only stored procedures,delete permission can be revoked on certain tables for the application etc…Since I have not much worked in a real production environment, I didn’t get a chance to try all these.As you know in the development environment developer is the king and he will be having all the permissions.
But last week I got a good opportunity to play with these permission settings.I had to grant update and select permission for a SQL database user on a particular view.Initially we did using the SQL Server Management Studio.It is very easy.Below are the steps.
  1. Navigate to <Your Database>->Security->Users and double click on the user to whom you need to grant or revoke permission.
  2. On the Database user window select the securables page.
  3. There are 2 portions at the right side.Top section is to select securables and below section for the Permissions on the selected securable.
  4. Add your securable using the search window.The search window can be poped up using the search button.
  5. You can add a securable in 3 modes.Specific database object,all object of specific type or all objects in a schema.
  6. Once you add the securable using the search window, the bottom pane will show you what are the permissions you can set on those db objects.
Very easy. Isn’t it? This will work out only if you are delivering your database as a backup.If you are planning to deliver the scripts, you can get the corresponding scripts on clicking the script button at the top of the Database user window.
Below is the Database user window where you can set the permissions visually.
This is the script generated when clicked on the button”Script Action to new query window”

use [AddressBook]
GRANT SELECT ON [dbo].[PersonView] TO [joymon]
use [AddressBook]
GRANT UPDATE ON [dbo].[PersonView] TO [joymon]

Friday, June 10, 2011

Querying all tables in a database using sp_foreachtable

Last week one of my colleague was struggling to write a sql script.After some time he came to me.His requirement was simple, he need to truncate all the tables in the DB.He wrote a script.But it was throwing exception because of foreign key relation ships.My first answer was to run that script for 10 or 20 times so that the child tables will get deleted in initial runs and in primary key tables will get deleted in last runs.A real hack!!isn’t it? I suggested this because he need to just clear a database for one time.

But after some time I thought of solving it properly.For that I didn’t try writing a new script or correcting his script.Just googled and got the below link.

It says 2 lines of code as follows.

-- disable all constraints

-- delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'

Done.I again enforced the concept of modern programming especially in .Net and SQL server.ie “If you think your problem might be faced by somebody else, google it before even attempting”.

Also I would like to tell about the undocumented procedure sp_msforeachtable.Its very useful when you need to execute same operation in all the database tables.More details can be found in this link.Now a smart programmer you might have though whether there is anything for looping all the databases in the sql server instance.Yes its there.the name is sp_foreachdb.

Oh that is great.Everything related to db objects are available as for each?Unfortunately NO.But you can create your own foreach by following this article.Yes you can create your own  sp_foreachview,sp_foreachsp etc…I don’t think developers including sql developers need this very frequently.But the DBAs surely need this.

Happy scripting.

Wednesday, June 8, 2011

Schema.org a new SEO technique

How search engines identify your site and its contents is a big question ever.We had tried so many techniques to list our sites in the search engines based on some key words.But if somebody adds more words their site will overtake ours. Now the major search engines unite together to have a common schema for the web data. ie as a web developer we can tell the search engine about the nature of our site. The schema contains itemscope and itemprops using which we can specify the category of the data in the site. If our site is to list the movies we can specify our movie name html element as Movie and director name as itemprop director.

This enables the search engines to identify that this data is about a movie and the director name is mentioned.So search engines can store them structurally which help to give better search results.one eg is given below which I took from google blog.

<div itemscope itemtype ="http://schema.org/Movie">
<h1 itemprop="name">Avatar</h1>
<span>Director: <span itemprop="director">James Cameron</span> (born August 16, 1954)</span>
<span itemprop="genre">Science fiction</span>
<a href="../movies/avatar-theatrical-trailer.html" itemprop="trailer">Trailer</a>

see the new words itemscope,itemtype and itemprop.These are the special schema words to identify the category of the data in our site which helps the search engines to build a good search index .

So what are the keywords or categories available? the schema.org answers this.You may get the details from their site.http://schema.org/docs/gs.html

As I mentioned earlier ,another important aspect is this is a combined effort from the major search engines such as google,yahoo & bing.Clicking on the link will give you what they say about schema.org.

Sunday, June 5, 2011

SQL OpenRowSet

In simple words OpenRowSet is to fetch data from other sources which are not linked to the sql server in which a query is executing. When we say other data source it can be another sql server instance which is not linked to our sql server instance.

For example if you want to know the details of available databases in another sql server (local\sqlexpress) from your local instance of sql server, you can use the below query to execute

'select * from sys.sysdatabases')

But when you execute this most probably you will receive an error as follows

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

What should we do now?

As usual google and find out the answer.Its only about enabling ad hoc distributed queries as follows

sp_configure 'Ad Hoc Distributed Queries', 1;

Now run the query again.You will see the result.ie you have queried another independent data source from your sql server.You can even query non databases such as excel,xml etc…

This is the mechanism  if you want to communicate between 2 sql databases which are residing in 2 different server instances without using linked servers.Interesting.Isn’t it.

Note : If the fmtonly is on it will return only the metadata.If you want to get the real data ,make sure fmtonly is off using the below statement in the server where you are running the above sql.

set fmtonly off