Lsuse

Get Datase Users, Groups and Roles

Thursday, 26 January 2012 12:10 | Author: Demas Jaring |

declare @principal table
(	
	[Database] varchar(255),
	[default_database_name] varchar(255),
	LoginType varchar(25),
	srvLogin varchar(255),
	srvRole varchar(255),
	dbUser varchar(255),
	dbRole varchar(255)	
)


insert into @principal
exec sp_MsForEachDb '
use [?]
select ''?'',
sp.default_database_name,
[Login Type]=
case sp.type
when ''u'' then ''WIN''
when ''s'' then ''SQL''
when ''g'' then ''GRP''
end,
convert(char(45),sp.name) as srvLogin, 
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from 
sys.server_principals as sp 
join sys.database_principals as dbp on sp.sid=dbp.sid 
join sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id 
join sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left 
join sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left 
join sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id'


select * from @principal
GO

Last Updated ( Thursday, 26 January 2012 12:11 )

Get First day and last day of quarter

Thursday, 05 January 2012 08:53 | Author: Demas Jaring |


declare @date datetime
set @date = getdate()
-- Get first day of month quarter
select DATEADD(qq,DATEDIFF(qq,0,@date),0)
-- Get last day of month quarter
select DATEADD(qq,DATEDIFF(qq,-1,@date),-1)

Check DB Restore History

Friday, 23 September 2011 07:14 | Author: Demas Jaring |

-- Check the last restore from a database
USE MSDB 
GO 

SELECT TOP 1 *
FROM RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = DATABASENAME')
ORDER BY RESTORE_DATE DESC

Last Updated ( Wednesday, 28 September 2011 10:23 )

Guide To MCITP: SQL Server 2008 Administration

Friday, 12 August 2011 08:30 | Author: Mike McQuillan |

In 2009, I wrote an article outlining the study areas to concentrate on for the MCITP in SQL Server 2008 Database Development certification. Back then, my intention was to go straight on and complete the MCITP in SQL Server 2008 Database Administration. Unfortunately I moved to a new house in August 2009. After eighteen months of DIY I finally returned to my studies, and obtained the MCITP in Database Administration in July 2011.

This article outlines the areas you should cover when studying for the Database Administration MCITP, along with the exams involved and where you can find test questions.

The Qualifications.

There are two levels of SQL Server 2008 Database Administration certification:

To obtain the MCTS in Database Administration, you need to pass exam 70-432. To obtain the MCITP, you must pass both 70-432 and 70-450.

If you want to become a Microsoft Certified Master (MCM) in SQL Server 2008, you will need to pass both of these exams as a pre-requisite, as well as the two database development exams I have already written about.

Exams

The exams present questions in multiple-choice format. Here are the details for each exam:

70-432 - 45 questions in two hours
70-450 - 50 questions in two hours

70-432 is the basic exam and covers a range of general SQL Server administration areas. 70-450 is more advanced and covers all the areas of 70-432, plus higher-level DBA tasks such as high availability and infrastructure design. Be aware that all questions are multiple-choice (more on this later). However, 70-450 may include some drag and drop questions where you are asked to put a set of tasks into a particular order, e.g. restoring a piecemeal backup. I did not see any of these questions on my exam but I am assured they exist.

Two hours is how long you are actually given to answer the questions; you are allocated some extra time to answer survey questions and to optionally comment on the questions.

Last Updated ( Friday, 12 August 2011 08:38 )