Permissions error setting up PowerShell account privileges for SharePoint 2010

The short answer is "yes" for most activities you'll carry out via STSADM against SQL databases.

For the overwhelming majority of STSADM commands that execute against the SharePoint API directly (rather than scheduling tasks to carry out an action), the security context within which commands are executed is yours -- the signed-in user. As you've seen in the example you cited, your user account context is the one that will be used for the retraction. If you don't have the appropriate rights in SQL to carry out the operation it will fail (as you've seen).

This contrasts with most activities you'll carry out through the UI (that is, Central Admin). In the example you cited, retracting the solution via Central Admin would result in the command being executed within the context of the farm service account, as that account is the application pool identity for the Central Admin site. Result: the retraction would succeed even though you (personally) don't have permissions to the associated database.

If your environment is setup such that your account doesn't have admin-level access to the databases within the SharePoint farm, I would recommend carrying out as many activities as possible through the UI to avoid the type of security context issues you're encountering. You'll find you can do most of what you need to do that way. One notable exception that comes to mind, though, is adding a solution (STSADM -o addsolution) to the farm solution store -- no UI counterpart to the STSADM command exists.

Alternatively, you could do something similar to what MadlyAlive suggested (i.e., logging in with the farm service account) ... though local admin access for the farm service account is neither required nor recommended by Microsoft. You could also have your account granted the minimal set of permissions inside of SQL Server needed to carry out your operations.

For more discussion, see Microsoft's KB article at http://support.microsoft.com/kb/896148.

Rule of thumb recap: STSADM uses your account context, Central Admin uses the farm service account context.

I hope this helps!

Deleting the user accounts and recreating them seemed to do the trick. Tried logging on to the machine as the service account didn't work, so i figured there had to be something wrong with the account. deleted it, added it, now it's all working again..