Dear Readers,
You can check out IntelliSense Article on MSSQLTips.com.
Click Me to read…
Thanks,
Jugal Shah
Enabling IntelliSense and Refreshing IntelliSense Data in SSMS 2008
1 Reply
Category Archives: SQL Server
Script to find out Orphaned AD/Windows Logins
It is easy to find out the orphaned SQL logins by comparing the SID of SQL Login and User, but what in case of windows login.
Take an example if windows login is dropped and it is still exists in SQL Server. You can find out all the delete windows login which is orphaned in SQL Server, using below procedure.
Sp_validatelogins
Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.
CREATE TABLE #dropped_windows_logins
(
[sid] VARBINARY(85),
[name] SYSNAME
)
INSERT #dropped_windows_logins
EXEC sys.Sp_validatelogins
SELECT *
FROM #dropped_windows_logins
DROP TABLE #dropped_windows_logins
Script to create Folder/Directory using SSMS
One of my blog reader requested that, how to create the directory using Script/SSMS. Here is the answer.
— Below query will list out the directories on C drive
EXEC MASTER.sys.Xp_dirtree ‘C:\JugalA’
— Below query will create the folder JugalA on C drive
EXEC MASTER.dbo.Xp_create_subdir ‘C:\JugalA’
— Below query will create the folder JugalB on C:\JugalA drive
EXEC MASTER.dbo.Xp_create_subdir ‘C:\JugalA\JugalB’
Query to Check the SQL Server Restart time
You can simply run one of the below query to check the SQL Server last restart time.
SELECT Dateadd(s, ( ( -1 ) * ( osd.[ms_ticks] / 1000 ) ), Getdate()) AS serverrestartdatetime,
osd.sqlserver_start_time
FROM sys.[dm_os_sys_info] osd;
OR
SELECT name,
crdate
FROM sys.sysdatabases
WHERE name = 'tempdb'
aiDeeva.com 