When was the 'sa' password changed ?

SELECT LOGINPROPERTY('sa','PasswordLastSetTime')
Go

To identify Clustered and Non-Clustered Primary key table(s) in the Database ?

To fetch all the Primary key tables in the current Database.

Use SQLServerBuddy
Go

SELECT
O.[NAME] 'Table Name',
I.[NAME] 'Key Name',
CASE I.[TYPE] WHEN 2 THEN 'Non Clustered Primary Key' ELSE 'Clustered Primary Key' END 'Clustered /NonClustered'
FROM SYS.INDEXES I JOIN SYS.OBJECTS O
ON (I.[OBJECT_ID] = O.[OBJECT_ID])
WHERE O.[TYPE] = 'U'
AND I.IS_PRIMARY_KEY = 1
ORDER BY 1
 
The column Type in Sys.Indexes DMV have the following values
 
0 - HEAP
1 - CLUSTERED
2 - NONCLUSTERED

A particular stored procedure used in which JOB

Here, we want to search a particular stored procedure(USP_Proc1) used in wich JOB.

Use Master
Go
SELECT V.name 'JOB Name' FROM msdb.dbo.sysjobsteps s JOIN msdb.dbo.sysjobs_view v
ON(S.job_id = V.job_id)
WHERE S.command LIKE '%USP_Proc1%'

Scheduled JOBs in current instance

We can listout what are all the JOBs scheduled in our Instance.

1. To listout scheduled jobs in current instance:
USE master
Go

EXEC msdb.dbo.sp_help_job
(or)
SELECT * FROM msdb.dbo.sysjobs_view

2. To listout particular JOB's Steps, Schedules,...Etc

DECLARE @JOBID UNIQUEIDENTIFIER

SELECT @JOBID = job_id FROM msdb.dbo.sysjobs_view
WHERE name = 'JOB Name'

EXEC msdb.dbo.sp_help_jobstep @job_id=@JOBID

EXEC msdb.dbo.sp_help_jobschedule @job_id=@JOBID

The SELECT permission was denied on the object 'sys...', database 'msdb', schema 'dbo'.

When we try to access the SQL Server Agent related system tables, The following Error occurred

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'sys...', database 'msdb', schema 'dbo'.

What are the Database role needed to access the following SQL Server Agent system tables ?

USE MSDB
Go

The following system tables needed SQLAgentOperatorRole database role
sysalerts,
sysnotifications,
sysoperators

The following system table needed SQLAgentUserRole database role
syscategories

The following system tables needed TargetServersRole database role
sysdownloadlist,

sysjobs,
sysjobservers,
systargetservers,
syssubsystems

The following system tables needed db_Owner database role
sysjobactivity,

sysjobhistory,
sysjobschedules,
sysjobsteps,
sysjobstepslogs,
systargetservergroupmembers,
systargetservergroups,
systaskids,
sysproxies,
sysproxylogin,
sysproxysubsystem,
sysschedules,
syssessions

Granting Database Role to a Login:
USE msdb
GO
EXEC sp_addrolemember N'RoleName', N'Login'

The user does not have permission to perform this action.

Scenario:
We have created a new Login (ie: Test) and Login into this account and able to access the Databases.

When I try to execute DMVs like SYS.dm_exec_connections, The following Err occurred.

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

So, The login required some Server permission to access the DMVs. correct ?

Login into sa account and grant the following permission to login: test

USE master
GO
GRANT VIEW SERVER STATE TO [test]

The View Server State permission granted to the Test Login.


Now we can access the DMVs.

Migrating MS Access 2003 Database to SQL Server 2008

What is Migration ? Migrating from one Product type to another Product type.
ie: MSAccess to SQL Server, Oracle to SQL Server

What is Upgradation ?
Upgrading from one Edition/Version to another Edition/Version within the same Product type.
ie: SQL Server 2005 to SQL Server 2008

Here, we going to Migrate Microsoft Access 2003 Database to SQL Server 2008.

1. We have SSMA (SQL Server Migration Assistant) tool which can be downloaded from the url given below

http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx

2. Once launched the page, Click the appropriate link as given below 













3. Once click the link, Download page will appear and Click Download Button. Once download completed.

4. Double click the downloaded file.



 




5. Follow the screens as given below















6. Click Next.















7. Click Download Button.















8. Once downloaded the OLEDB provider for MSAccess, Double click the .exe file.



9. Follow the screen as given below
















10. Click Next button and follow the screen sequence.











11.















12.















13.















14.















15.















16.















17.















18.















19.
20.










21.Licence Management required to login MSN Live ID













22.
 












23.















24.












25.













26.













27.













28.













29.













30.














31.If we want to retain the link between MSAccess Database and SQL Server, Just Check the Link Tables check box, If you don't want to link the tables, Just uncheck.













32.













33.













34.













35.













36.












37.



Microsoft Access 2003 database successfully migrated to SQL Server 2008.