This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Wednesday, December 29, 2010

Server Roles in Sql Server 2005

Server Role is used to grant server-wide security privileges to a user.

Sql Server 2005 comes with following fixed server Roles are:
1. bulkadmin
 2. dbcreator
3. diskadmin
4. processadmin
5. securityadmin
6. serveradmin
7. setupadmin
8. sysadmin

Each Role has different power to play with SQL Server Instance,Like Role dbcreator can create, alter,

drop, and restore any database. It is not easy to remember that what a Role can do all.
Sql Server provides a System stored procedure(sp_srvrolepermission) to list the permissions of Server Roles.

Try this statement on any database:
EXEC sp_srvrolepermission dbcreator

Will list the all permissions associated with dbCreator Server Role in tabular form.
To get list of permissions for all Roles, run SP without parameter.

Using this SP we can easily understand all Server Roles and make a better Decision to implement security.

Tuesday, December 21, 2010

Truncate Parent Table

If the Foreign key references is estabilished between tables then
we can not fire Truncate Satement on Parent table weither records exists in Parent table or not.

We need to delete the reference then we can truncate table.

Monday, December 20, 2010

Execution Plan Of Encrypted Stored Procedure

If a stored procedure is encrypted, then we cannot see the execution plan.

But we can see In SQL Server 2000 .

Wednesday, December 8, 2010

Configure TFS 2010 On Visual Studio 2005

Can we use TFS 2010 with Visual Studio 2005??

Yes we can, Microsoft provides facilities to use TFS 2010 on Visual Studio 2005 with Installing a forward compatibility update .
Install the following components in below order:

1. Visual Studio 2005
2. TFS Team Explorer 2005
3. Visual Studio 2005 Team Suite SP1
4. Visual Studio Team Explorer 2005 forward compatibility update to support TFS 2010

The order is must here.

If you face any application error to Install Visual Studio 2005 Team Suite SP1 component.
then give a try to Extract the EXE and run setup.

Some Important Points:
1. Close Visual Studio before Installion
2. If you have Visual Studio 2005 installed already with Service pack 1, then I think you need to uninstall the Visual Studio first,
then install all components in order.
3. The Installation order is really important to make it work.


How to Install SQL Server 2005 Client only?

1. Simply Browse your SQL Server Setup CD/Folder.
2. Go to Tool Folder
3. Run Setup.exe

The following components will be installed:
2. Visual Studio Integrated Development Environment(IDE) 2005
3. SQL Server Books Online
4. SQL Setup Suppport Files
6. SQL Native Client
7. QWC11
8. SQL Server Backward Compatibility Files
9. Workstation Components, Books Online, Development Tools (SSMS,VS)