If you're in an environment where you can't get psexec onto the system, you can use the AT command and schedule it to start a minute or two after you execute the command. By default, task scheduler tasks that are scheduled using the AT command run as System, though that should normally be changed to prevent the AT command from being exploited in this manner.

KBK, please explain "use the AT command" if you have time. tks.

The AT command is a command-line hold-over from the days before Task Scheduler. It allowed you to schedule processes to run on Windows systems. When Task Scheduler was added to the OS, AT wasn't eliminated. Instead, using AT creates a Task Scheduler task, but running with the security account specified for any tasks coming from the AT command.

More at TechNet: http://technet.microsoft.com/en-us/library/cc772590(v=ws.10).aspx

Ron please see:

http://support.microsoft.com/kb/313565

The KB article says Windows 2000 (and Microsoft will try to coerce you to use today's GUI equivalents, e.g. task scheduler) but AT is alive and well even in Windows 8.

thanks, guys.

Aaron, thank you for posting this.  I have had to do this the hard way in other instances and this may save me quite a bit of time when I run into this again. 

I would reiterate the comment about not using the SA account for other programs though.  Generally, a policy of least priveleges is good for security, and it is fairly rare that a program needs admin level priveleges to the SQL Server (though it does come up).  Even when a program really must connect to the server with its own admin credentials, I would give the program its own account if only to avoid problems when I change the SA password and to make tracking the source of activity a little bit easier.

Does this mean that if you have administrative access to a system that you can break into SQL Server? Or am I missing a bit here?

Gene, yes, if you give someone local administrator rights, there are many ways they can break into SQL Server, not just the one I showed here. This is just the one that has least impact on a running system. SQL Server tried to make it harder for local IT administrators (but non-SQL administrators) to gain access to SQL Server, but basically a local administrator owns the machine and can take ownership of anything running on it.

The security problem this highlights is a different issue altogether. Keys to the castle, and all that.

Aaron 

I am not certain you could call this a security issue.  A local admin probably should be able to get into SQL Server, and even if you truly structured it so they couldn't, then that still does not protect the data from them since they can access the files.

You could limit their access to the data through encryption, and there are types of logging that can make it very difficult to make changes without leaving tracks, but a local admin will always be able to cause a vast amount of mischief if they were so inclined and you should never give anyone admin access unless you can trust them.

Tim,

  it is a security issue because it is a bypass of a security control. Of course, you expect such controls to be a deterrent, not 100% effective. From an audit perspective, if the data is that sensitive, you need to impose other controls, like event auditing to see who is logging on and reporting that accordingly.

If your client SQL Server is under SQL 2012 you can also (proc)dump the sqlservr.exe process. Then run strings.exe to filter it. Then search in the filtered file for the following string: <instancename>sa (for example if you instance name is TestSQL then you searching for TestSQLsa) Right next to this string you will see the sa password. Watch out the procdump will cause some hanging on the instance while it dumps everything from the memory to the disk.

Source:

http://sqlblog.com/blogs/argenis_fernandez/archive/2012/01/20/on-the-topic-of-lost-sa-passwords-on-sql-server-2000.aspx

@Peter that sounds a lot more invasive and dangerous than using PSExec.exe or even incurring some downtime using he other methods.

Peter, note Argenis' caveat: you can only do this if there's a currently established session with the sa login. No session, no password. Basically, the password for an SQL Server-based login is visible in memory on the system if the session is established.

Yep, I just mentioned this as an alternative. Impersonating System is a safer way.

@K Brian Kelly  You have a good point; I can see how this could be considered a security issue.  With that said, I don't really expect locking my front door to keep someone out if I gave them the key to my garage door. 

As Aaron said, Admin on the machine is essentially the keys to the castle.  You can put some limitations on an admin, especially through use of cryptography, but they can legitimately override most restrictions and can circumvent many other restrictions if they are willing to be more devious (an admin can likely put in place a key logger to eventually get those encryption keys for instance). 

This did not work for me. I have to change my path information to locate the Ssms.exe directory one I did that I excuted the command with the new path information, and recived an error..

PsExec could not start D:\Program on USCLA123A:

The system cannot find the file specified.

Am I missing something here?

Jason, you might have to surround the path to Ssms.exe with double quotes, as I did in the sample code. It looks like it is being truncated at the first space.

Works like a charm!!!!!

Thank you very much for this topic!!!!!!!!!!!!!!!

You saved my week!!!!!!!!!!!!!!!!

Thanks a lot, this saved me a lot of troubles!

Aaron, good stuff.

However, trying to understand on how it works when the NT AUTHORITY\SYSTEM account is removed from SQL Server 2012 instance (by default)?

Thanls

Prashant, this doesn't happen by default (unless you're confusing NT AUTHORITY\SYSTEM with Builtin\Administrators).

If you *manually* delete NT AUTHORITY\SYSTEM, *and* haven't added yourself and/or a domain admin as an administrator account, *and* lost the sa password, that seems like a perfect storm to me, and I can't say I have a whole lot of sympathy in that case. Shut down the service, install a new instance somewhere, and restore your latest backups. If you don't have recent backups available, then I have even less sympathy, as even more preventable things have gone horribly wrong. Grab the mdf/ldf files and try to attach them to the new instance.

Thanks Aaron. I think you answer the question.

However, I was talking about the change in SQL Server 2012, which says that

             BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.

http://msdn.microsoft.com/en-us/library/bb500459.aspx

Prashant that may be what the documentation states, but every single SQL Server 2012 instance I can currently access has NT AUTHORITY\SYSTEM in the sysadmin role. I did not modify this and these are not upgrades; it is how SQL Server 2012 was installed. Do you have any instances where you have not made a change and this is not the case?

That said, if you have an installation where NT AUTHORITY\SYSTEM is not in the sysadmin role (whether it has been removed or SQL Server didn't put it there in the first place), then yes, you are out of luck. You'll be able to connect (assuming the login hasn't been removed altogether), but you won't be able to set the password for existing logins, create new logins, etc.

Hi Aaron, I just checked a couple of my servers and the NT AUTHORITY\SYSTEM is in the PUBLIC server role. There are a bunch of NT SERVICE \ logins that have the sysadmin role. Not sure if this a change or not.  Both instances I looked at are running 11.0.2100.

Greg ok, in any case, like I said, you can either add them to the sysadmin role if they're not already and in the future you may want to use this technique, or know that it won't work if you leave it as is.

Aaron,

Yes, I checked with all of my SQL Server 2012 instances (around 8) and none of them is provisioned using NT AUTHORITY\SYSTEM, which is inline with what the Microsoft documentation Says. I am still wondering, how it comes for you and Greg (though with different roles, Sysadmin for you and Public for Greg).

I will try to Provision SQL Server 2012 on one for the fresh Windows 2008R2 server and will update here if getting the different result than earlier.

Thanks

Prashant T

Prashant, it doesn't really matter. What it means is if NT AUTHORITY\SYSTEM is not on your system, or is not in the sysadmin role, then yes, you are absolutely correct, this method won't work. If you want it to work, then add NT AUTHORITY\SYSTEM. If you don't want it to work anyway, there's nothing to do.

Thanks Aaron.