modify SQL Server logins

Archive for modify SQL Server logins

May
1

Your poor man’s SQL Server log shipping-PowerShell version

by edwin

In 2008, I was privileged to be a part of a project to write a chapter for a book that was for a good cause. SQL Server MVP Deep Dives became an avenue for a lot of the SQL Server MVPs to share their expertise for the benefit of not just the technical community but of the beneficiary.

I wrote a chapter for the book based on this blog post some three years ago and one of the recommendations I did was to convert the VBScript scripts to Windows PowerShell. So, here it is.

I've converted the VBScript that does the checking of the folders containing my latest LOG backups and restoring them in NORECOVERY mode to Windows PowerShell. What is fascinating is he fact that if the process I've outlined in the blog post is followed thru, adding another database to be configured for log shipping was as easy as creating regular LOG backups via TSQL scripts or database maintenance plans - no need to add an entry for every database that will be configured. How cool was that?

So, here's the Windows PowerShell version of the custom restore LOG backup command

$logBackupFolder="your LOG backup location here or a parameter variable"

##Variable for time duration - the amount of time you need to generate and restore LOG backups
$1HourOld = [DateTime]::Now.AddHours(-1)

##Retrieve folders and files in the specified directory
foreach ($databaseName in (Get-ChildItem $logBackupFolder Where { $_.PsIsContainer }) )
{
foreach ($logName in (Get-ChildItem $logBackupFolder\$databasename where {($_.CreationTime -ge $1HourOld) -and ($_.Extension -eq ".trn")} SELECT name))
{

$logBackupPath = [System.String]::Concat("'$logBackupFolder\",$databasename,"\",$logName.Name,"'")
$restoreSQL = "RESTORE LOG $databaseName FROM DISK=$logBackupPath WITH NORECOVERY" Out-File D:\$databaseName.sql

Invoke-Expression "osql.exe -SYourSQLServerInstanceHere -E -iD:\$databaseName.sql"

}
}

You might be wondering why I am using osql.exe instead of sqlcmd.exe. Well, it's still there even if you have SQL Server 2008. Which means this script will work even for SQL Server 2000 instances as long as you install Windows PowerShell on the machine running SQL Server.

And all of that for a mere 17-liner script. I could write it even less than that but I'm a big fan of script readability. Now, that's a good enough reason to dig deeper on what Windows PowerShell has to offer

Categories : Edwin Sarmiento

Jun
16

Check the last backup date in SQL Server using WIndows PowerShell

by edwin

This article highlights how to use Windows PowerShell to retrieve database properties using SMO. Notice how easy it is to check the database properties using pretty common syntax

One of the challenges I have when I was starting out as a SQL Server DBA was to check for the last backup date for a database. One way to do this is to find out which tables in the MSDB database contain the records of the backup history. What's really challenging here is the fact that you would have to look at the tables and their corresponding relationships which, apparently, MSDB doesn't have. You simply have to rely on what SQL Server Books Online has to say. Plus, the MSDB database will only contain records for databases with backups. What about those without?

For SQL Server 2005, the script below displays the last backup date of all the databases on your SQL Server instance. This script is from the MSDN Code Gallery

SELECT 
T1.Name AS DatabaseName, 
COALESCE(CONVERT(VARCHAR(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') AS LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

You can simply replace the sys.sysdatabases table with master.dbo.sysdatabases for SQL Server 2000

Below is the equivalent script using Windows PowerShell.

$instance="Your_SQL_Server_Instance_Name"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases
$dbs | select Name,LastBackupDate, LastLogBackupDate | format-table -autosize

The only thing to note here are the last two lines - the one that creates an instance of the database object and the one that displays and formats a few of the database object properties. The first few lines will be the same for just about any PowerShell script that will access SQL Server using SMO

Categories : Edwin Sarmiento

Apr
6

Changing a SQL Server 2000 login

by edwin

WARNING: This is not a recommended approach. Use at your own risk

While SQL Server 2005 has the ALTER LOGIN statement to change the properties of a SQL Server login account, SQL Server 2000 does not have such a command. Unfortunately, there are cases where you need to simply rename the login due to a misspelled name or a change management policy. The proper way to do it in SQL Server 2000 is to create the new login, map the permissions and roles of the existing login that you wish to change to this new login and, then, drop the old login. I wouldn't want to go thru that if I only have to rename the login. The only simpler way to do it is to modify the system tables. As I've said, it is not recommended to modify the system tables and/or objects directly so bear in mind that doing this would be at your own risk. This would also require that you torn on allowing ad hoc updates to system tables and turning it off afterwards

sp_CONFIGURE 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO

UPDATE db..sysusers
SET name='newLogin'
WHERE
name='oldLogin'

UPDATE master..sysxlogins
SET name='newLogin'
WHERE
name='oldLogin'

sp_CONFIGURE 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO

A similar stored procedure is described here

Categories : Edwin Sarmiento

Archive for modify SQL Server logins

Your poor man’s SQL Server log shipping-PowerShell version

Check the last backup date in SQL Server using WIndows PowerShell

Changing a SQL Server 2000 login

Search

Support