7 Responses to How to tell if you have instant initialization enabled?

1. Josh says:

   August 18, 2008 at 8:25 pm

   I must say, I love instant initialization. It’s awesome. But I always wondered why it’s not available for the log file. I assume there’s a technical reason… but what is it? Does it depend on having the rest of the file be zeroed out? Doesn’t it already know where it’s start and stop points are anyways, since the log is circular?

   Reply
2. Scott R. says:

   August 26, 2008 at 11:03 pm

   Paul,

   Thanks for this post.

   Getting the list of privileges assigned to a certain user is useful for the reasons you described (checking the “Perform volume maintenance tasks” privilege – SeManageVolumePrivilege) as well as other reasons (such as confirming the “Lock pages in memory” privilege – SeLockMemoryPrivilege).

   I learned that WhoAmI can only show the privileges of the logged-on user. It can’t be asked to show the privileges of another user, process, or service. I have concerns with enabling xp_cmdshell, even for short time periods.

   After a bit of looking, I found a free alternative from Microsoft (Mark Russinovich – Sysinternals) called AccessChk (http://technet.microsoft.com/en-us/sysinternals/bb664922.aspx). It offers command line options to specify a user (-a), a service name (-c), or a process name (-p) – instead of only reporting on the logged-on user.

   As an example, I was able to run this command from a command window:

   AccessChk –p sqlservr.exe –f –q –v

   This command returned the service account used by the SQL Server instance and its assigned privileges – without having to enable xp_cmdshell!

   One minor difference between AccessChk and WhoAmI: AccessChk shows the privilege name (SeManageVolumePrivilege, etc.) and the state (enabled / disabled) for each privilege, while WhoAmI does that and adds a description column (Perform volume maintenance tasks, etc.). Most of the privilege names are fairly self-descriptive, so I don’t consider it a major gap. And with a relatively small number of privileges that exist, one could come up with a privilege name / description lookup pretty easily, if needed.

   I haven’t tried running AccessChk on servers with multiple active DB instances, but I suspect that the example command line above will return feedback on all instances (since it doesn’t use a path name for the process name, and since the process option also allows an * to show all processes). Let me know if you happen to try it that way.

   Thanks for sparking the interest in this topic. I enjoy learning new ways of being better informed about a given environment.

   Scott R.

   Reply
3. Glenn Spies says:

   December 11, 2008 at 7:03 am

   For those of you who ran into even more security when tryin to enable the cmdshell, here is a very slightly modified script to enable advanced options as well, then turn them off afterwards:

   USE master
   GO
   EXEC sp_configure ‘show advanced options’, 1
   GO
   RECONFIGURE WITH OVERRIDE;
   GO
   EXEC sp_configure ‘xp_cmdshell’, 1;
   GO
   RECONFIGURE WITH OVERRIDE;
   GO

   CREATE TABLE #xp_cmdshell_output (Output VARCHAR (8000));
   GO

   INSERT INTO #xp_cmdshell_output EXEC (‘xp_cmdshell ”whoami /priv”’);
   GO

   IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE ‘%SeManageVolumePrivilege%’)
   PRINT ‘Instant Initialization enabled’
   ELSE
   PRINT ‘Instant Initialization disabled’;
   GO

   DROP TABLE #xp_cmdshell_output;
   GO

   EXEC sp_configure ‘xp_cmdshell’, 0;
   GO
   RECONFIGURE WITH OVERRIDE;
   GO
   EXEC sp_configure ‘show advanced options’, 0
   GO
   RECONFIGURE WITH OVERRIDE;
   GO

   Regards,Glenn

   Reply
4. Patrick says:

   August 17, 2009 at 9:22 am

   Paul, great post, but I do have a (hopefully useful) comment.

   Seems to me that query of the temp table needs to also include the condition text of ‘Enabled’ as well as containing the text ‘SeManageVolumePrivilege’ to indicate that the privilege is indeed held, as the results of ‘WHOAMI /PRIV’ will always return all privileges and their respective status for the current login.

   So this line:
   IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE ‘%SeManageVolumePrivilege%’)

   Should be:
   IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE ‘%SeManageVolumePrivilege%’ and Output LIKE ‘%Enabled%’)

   Otherwise you get a false positive. That is unless I haven’t gotten enough sleep (and that’s entirely possible ;) ).

   Thanks again!

   -Patrick

   Reply
5. SQL Server Installation Checklist | EnterpriseDBA says:

   January 9, 2013 at 1:35 pm

   [...] Verify Instant File Initialization is enabled (Paul Randall’s Blog) [...]

   Reply
6. Follow-on from instant initialization privilege checking - Paul S. Randal says:

   January 17, 2013 at 4:55 pm

   [...] my previous post on checking whether a SQL instance is able to use instant initialization (see here), I had a discussion with Scott R., who regularly comments on blog articles. He proposed an [...]

   Reply
7. Creating, detaching, re-attaching, and fixing a suspect database - Paul S. Randal says:

   January 17, 2013 at 4:56 pm

   [...] of this is that if the SQL Server instance doesn't have instant initialization enabled (see How to tell if you have instant initialization enabled?), then creating the dummy database could take a long time if the data files are very big. This [...]

   Reply