jump to navigation

Database Impersonation with EXEC AS in SQL Server October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment
Database Impersonation with EXEC AS in SQL Server
SQL Server 2005/2008 provides the ability to change the execution/security context with the EXEC or EXECUTE AS clause. You can explicitly change the execution context by specifying a login or user name in an EXECUTE AS statement for batch execution or by specifying the EXECUTE AS clause in a module (stored procedure, triggers and user-defined functions) definition. Once the execution context is switched to another login or user name, SQL Server verifies the permission against the specified login or user (specified with EXECUTE AS statement) for subsequent execution instead of the execution context of current user. To learn more about this feature and how it works click here.

Backup and Restore SQL Server databases programmatically with SMO October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

Backup and Restore SQL Server databases programmatically with SMO

In this article I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases programmatically with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO. Click here for more details….

Generate SQL Scripts for database objects with SMO October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

Generate SQL Scripts for database objects with SMO

In this article I take about how you can generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically. Click here for more details….

Accessing SQL Server programmatically with SQL Server Management Objects (SMO) October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

Accessing SQL Server programmatically with SQL Server Management Objects (SMO)

SQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO) which was used for SQL Server 2000. In this article, I discuss how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language. Click here for more details….

Reorganize and Rebuild Index in SQL Server 2005 and 2008 October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

Reorganize and Rebuild Index in SQL Server 2005 and 2008

Once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation. In this article, I am going to discuss the different methods and its feasibility to fix the identified high fragmentation levels by Reorganize and Rebuild, click here for more details….

Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement September 30, 2009

Posted by Arshad Ali in Database Administration, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment
Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using “EXEC AT” to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions. This article talks about this new way of executing dynamic SQL scripts on the remote server in SQL Server 2005 and 2008.