Monday, October 10, 2011

How to document logins with server roles

Recently a customer asked me if I could write a script that could document which logins on the sql SQL Server that was member of a server role.

WHEN isntgroup=1 THEN 'Windows Group'
WHEN isntuser=1 THEN 'Windows User'
WHEN isntuser=0 THEN 'SQL Login '
END LoginType,
loginname as [Login Name],
CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END isSysAdmin,
CASE securityadmin WHEN 1 THEN 'Yes' ELSE 'No' END isSecurityAdmin,
CASE serveradmin WHEN 1 THEN 'Yes' ELSE 'No' END isServerAdmin,
CASE processadmin WHEN 1 THEN 'Yes' ELSE 'No' END isProcessAdmin,
CASE diskadmin WHEN 1 THEN 'Yes' ELSE 'No' END isDiskAdmin,
CASE dbcreator WHEN 1 THEN 'Yes' ELSE 'No' END isDBCreator,
CASE bulkadmin WHEN 1 THEN 'Yes' ELSE 'No' END isBulkAdmin,
createdate LoginCreatedDate
from sys.syslogins
where hasaccess=1

Wednesday, September 28, 2011

How to make your SQL Server create database files instantly

The trick is "Instant File Initialization" - an NTFS feature that, when enabled for the SQL Server service account, creates the database files almost instantly - even with TB of size.

Note that this trick will also reduce your restore time !
How to: http://www.mytechmantra.com/LearnSQLServer/Database_Instant_File_Initialization_Feature.html

Remember that this feature does not work with TDE enabled.