select m.Name, isnull(p.name,”) as Role
From sys.database_principals m left join
sys.database_role_members r on r.member_principal_id = m.principal_id left outer join
sys.database_principals p on p.principal_id = r.role_principal_id
where m.type =’s’
order by Role desc, m.name

USE AdventureWorks;
GO
SELECT name AS index_name,
STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes
WHERE object_id = OBJECT_ID(‘Person.Address’);
GO

Instead of ‘Person.Address’ … use your own table name.

I found this at this site:  http://msdn.microsoft.com/en-us/library/ms190330(v=sql.90).aspx

When i first investigated this issue, i focused on the SQL error logs…because I have extensive experience with them…and they have generally served my purposes. This time, because the clusters are virtual i found better evidence in the Cluster event logs. In fact in the “Cluster Event” logs i also found all that i was used to finding in the SQL logs. In addition, i found the logs faster (by far) the SQL Logs. Also they are much easier to search thru, and you’ll find a wider filter capability. Lastly you can save your search criteria. These are all reasons to switch to the new, native Windows’ event logs when on a new, modern box.

Learning point: If you’re on a modern cluster…seek out the “Cluster Event Logs” and do your research there. You’ll save time and have an easier experience doing so. (Modern, here, is defined as Windows 2008 or newer.)

declare @version varchar(10)

select @version = ltrim(substring (@@version, CHARINDEX(‘.’,@@version)-2,2))

If @version = 10

select

convert(varchar(5), @version) as [Version],

convert (varchar(10),locked_page_allocations_kb)  as LockedMemory

from sys.dm_os_process_memory

else

select

convert(varchar(5), @version) as [Version],

convert (varchar(10),sum(awe_allocated_kb) ) as LockedMemory

from sys.dm_os_memory_clerks

select  ltrim(substring(@@version,CHARINDEX(‘.’,@@version)-2,2))

Steve

Create Nonclustered index idx_backupset_finishdate ON backupset (backup_finish_date) INCLUDE (backup_set_id, media_set_id)

What was taking 20 minutes now takes 1 minute.

Enjoy!

First, it is very useful in such situations to know what “user” is in ‘control’.  Thus, the ‘select user_name()‘ variations.  For information on the choice of “Login or User” see execute as.  Finally, don’t forget to “revert”!

In short, the solution is posted below:

/*
GRANT IMPERSONATE ON USER:: User1 TO [ddd\user2];
GO
ALTER DATABASE  myDb SET TRUSTWORTHY ON
 */
 ———————————————–
select SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();
use MyDB  ;
–execute as login = ‘MyDBuser’;
execute as user = ‘MyDBuser’;
select SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();
select top 10 * From Server1.MyDB.dbo .repl_to_test ;
revert;
selectSUSER_NAME(), USER_NAME();

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 208, Level 16, State 1, Server UFOsqlAB, Line 1
Invalid object name ‘CountySalesTax’.

What to do?  The solution?  Simple actually.  Include the “use database;” before the query as in:

Exec msdb.dbo.sp_send_dbmail
@recipients=’Steves@sqlinsight’,
@Query = ‘Use MyDb; select * from mytable’,
@body = ‘Job complete’;

SELECT
SD.database_id,
SD.name,
SF.name as FileName,
recovery_model_desc,
CASE SF.status & 0×100000 WHEN 1048576 THEN ‘Percentage’ WHEN 0 THEN ‘MB’ END AS ‘GROWTH Option’,
size * 8/1000 as DBsizeMb ,
CASE SF.status & 0×100000 when 1048576 then sf.growth else sf.growth * 8/1000 end as growth
FROM SYS.SYSALTFILES SF inner JOIN
SYS.DATABASES SD
ON SD.database_id = SF.dbid
where sd.database_id > 5
order by sd.name ,size desc