Monday, November 26, 2012

How to open a program in Windows 2008 and above using a different Windows account?


Before Windows Server 2008 there used to be an option to open a program like SQL Server Management Studio using different Windows account. You can open any program and say “Run as”.

This was helpful when you have domain accounts running SQL Services.

But in Windows 2008 and above "Run as" option has been removed.

So to open SSMS as some other domain account you can use following command in command prompt

runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Change domain\user and program file exe file

Wednesday, November 21, 2012

Steps to move system database files with scripts


TEMPDB
1.  ------get file details
use tempdb
go
select * from sys.database_files
2.  -----run following (MODIFY FILE NAMES AND PATH AS NEEDED)
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'S:\MSSQL\TempDB\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'S:\MSSQL\TempDB\templog.ldf');
GO
3.  -----stop SQL
4.  -----start SQL
5.  -----run following to vefiry
use tempdb
go
select * from sys.database_files

MSDB
1.  ------get file details
use msdb
go
select * from sys.database_files
2.  ------move files
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf')
GO
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf')
GO
3.  -----stop services
4.  -----copy files
5.  -----rename old files
6.  -----start services
7.  -----verify
use msdb
go
select * from sys.database_files

8.  -----if SQL Agent does not start check if service broker is enabled
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';

--USE master
--ALTER DATABASE ServiceBrokerTest
--SET ENABLE_BROKER;

MODEL
-----use same procedure as msdb

MASTER
1.  Stop services
2.  modify start parameters to new path
3.  copy files
4.       start services