Data Security with SQL Server Synonym’s

No Responses »
Apr 302010

I am a very critical DBA, when it comes to security and SQL Server is the Far most Secure database i have worked with, but here are some more concepts in SQL Server we can make use of to make the base objects more secure for client apps.

Synonym is an diff or alternative name that is used for a database object. So in turn all the application can use this single part name instead of using the multipart (two, three,four) names for reference a object.
This is also a security benefits in using a synonym for critical database objects, when need not be exposed.

How does this help a client application?
Well if you change the underlying object or structure of the object, you do not have to change the synonym and still use the same synonym name, without worrying about the base object.
The base object can be dropped and replace by the same name object.. like instead of a table you create a view with the same name, the synonym will work.

How to Create a synonym?

FOR Remote Servers (four Part Naming)
    CREATE synonym MySynonym for LinkedServer.DatabaseName.SchemaName.TableName

FOR Local Server
    Create Synonym MySynonym for DatabaseName.SchemaName.TableName

Select statement for a synonym?

    SELECT * FROM MySynonym

How to DROP a synonym?

    DROP Synonym SynonymName

So you can say a binding between a Synonym and its base object is by name only.

Synonym can be created on the object types.

Assembly (CLR) stored procedure
Assembly (CLR) table-valued function
Assembly (CLR) scalar function
Assembly (CLR) aggregate functions
Replication-filter-procedure
Extended stored procedure
SQL scalar function
SQL table-valued function
SQL inline-tabled-valued function
SQL stored procedure
View
Table1 (User-defined)

Only synonym owners, members of db_owner, or members of db_ddladmin can grant permission on a synonym.

you cannot reference a synonym that is located on a remote server (linked server)

Synonym can be created only on the following command types:
SELECT
INSERT
UPDATE
DELETE
EXECUTE
Sub-selects

Working on the synonym, affects the base object.
If you insert a Row in the synonym, which in turn inserts a row in the base object.

Read BOL for more info on this as most of the Topic is from BOL.

Posted by maninder at 8:53 am Tagged with: ,

Hypothetical Index and Performance Issue

No Responses »
Apr 282010

Running too much of the Database Tuning Advisor or Index tuning wizard(SQL 2000) on databases can end up “sometimes“ creating hypothetical indexes as the indexes that start with “_dta_index” or “”hind_%”

Too many of the indexes can pose a performance issues for your database. As the issue we had when one of our network admin was trying to be a DBA and worsened things on his end of the database on SQL 2005.

A Quick Clean up of those indexes got back the performance as required.

SELECT *
FROM sys.indexes
WHERE is_hypothetical=1

This Should be run against each database or rather, try the script below.

sp_msforeachdb ‘USE [?]; select quotename(db_name()) as dbname,* from sys.indexes where is_hypothetical=1′

Here is some more useful info from Microsoft. http://msdn.microsoft.com/en-us/library/ms190172.aspx

Posted by maninder at 2:27 pm Tagged with:

Download SQL Server SystemTable Posters

1 Response »
Apr 232010

if you haven’t download the system posters yet, you can download them at

SQL Server 2005 Poster : http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&displaylang=en

SQL Server 2008 Poster: http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c 

Well, these can be handy Tools among all, to quickly get a eye of where and what is where and what.

Posted by maninder at 11:09 am

no Time to Post…

No Responses »
Apr 222010

I haven’t been able to Post some really neat stuff, i wanted to because of the workload. I will be posting some nice interaction i had with SQL Server 2008 R2  and other Problem solution scenarios. So much to write and no time to write…

Posted by maninder at 1:31 pm

Good Post by Glenn Berry

No Responses »
Apr 152010

A very nice post by Glenn Berry about I/O DMV’s.. Great People and Great Posts.. I enjoy reading everybit of technical posts…

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!2991.entry

Posted by maninder at 7:28 am

Nice Posts by Paul Randal… must read

No Responses »
Apr 142010

Here are some nice Posts by Paul Randal a must read by a DBA… in excitement (Yahoooo Or Goooogle) both sound similar.. well they both have 2 oo’s too…..  well read here: http://www.sqlskills.com/BLOGS/PAUL/category/Misconceptions.aspx

Posted by maninder at 1:02 pm Tagged with:

Property Owner is not available for Database

No Responses »
Apr 062010

I got this error today on 2 of my production databases and this is how i corrected it:

When you right click on database and see the properties, This error will pop up.

Property Owner is not available for Database ‘[My Database]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

in the SQL Query run sp_helpdb and check the Database Owner to see if the value is NULL.

To Correct the database Owner issues use this T-SQL.

ALTER AUTHORIZATION ON DATABASE::[My Database] TO [sa]

Posted by maninder at 9:30 am
Older Entries
© 2010 D B A N A T I O N Suffusion WordPress theme by Sayontan Sinha
Better Tag Cloud