2

For example, when installing SQL Server 2008, I had to pick an account for the database engine to run as.

When I went to attach a 2005 database file with SQL Server Management Studio, there was a permission error preventing the database upgrade. I checked the database files, and the "SQLServerMSSQLUser$ComputerName$MSSQLSERVER" account has full permissions on the file.

So I figured that SQL Server Management Studio was running under my username, which did not have write permissions to upgrade the database file. So, I added full permissions for my username, and then it worked.

This incident is what led me to ask this question. How can I know for certain which account an action is running under? I thought the database engine would be handling attaching a database, but apparently not!

Furthermore, once the database was attached, it seems to have removed my username from the security list! So I imagine that if I detach the database, I'll have to reset my permissions on the files once again before I can re-attach the database.

Triynko
  • 3,418
  • 6
  • 31
  • 30

3 Answers3

2

Looks like SSMS is causing this confusion. My guess is that having to select the files in a dialog box means that the account SSMS is running under also needs permissions to the file.

As long as the service account has permission to the file, then you should be able to attach the database using t-sql.

Try using the sp_ attach _db You'll need to login as sa or use setuser or use the CREATE DATABASE... FOR ATTACH

If you really must use SSMS & the thought of T-SQL makes you want to throw-up then create a user account for the sql server service to run under & then use this account to start SSMS.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
  • SSMS gets the filename fine, so wouldn't it be the TSQL that's failing? The NTFS file permission is definitely a problem, since giving myself write access to the file fixes it. Interestingly, when detaching the file, it clears all NTFS file permissions and users, adds just my username, and grants me full-access permissions to the file, which allows reattaching. Interestingly, if I give myself read-only permission to the file once again, the database attaches as read-only; and that only works now because the initial upgrade was already completed, so write permission is no longer needed. – Triynko Jul 13 '09 at 18:14
  • Since it totally takes control over the file permissions during attach/detach, there are probably documented expectations for file permissions when performing that action in SQL Server 2008 that I just haven't encountered... and as I'm writing this I found the answer on Experts Exchange, so I'll post it here, see my answer. – Triynko Jul 13 '09 at 18:33
1

If you know the name of the executable file, then the Processes tab of Task Manager will show you the user name that is running that file.

Anthony Lewis
  • 909
  • 7
  • 8
  • sqlservr.exe process (running MSSQLSERVER service) is running as SYSTEM. ssms.exe is running as me. So, I know which username is associated with which process. What I'm asking is how do I know which process is performing which action. I though the database engine (running as SYSTEM) would be the one actually accessing the file, yet my username seems to need read/write access to it for it to complete. Why? – Triynko Jul 13 '09 at 18:20
  • I voted this one up, since it's somewhat useful/relevant to this problem. Who would have guessed that the service, which is setup to run as SYSTEM, actually deems it necessary to impersonate my account to attach the database! Strange, but it's all there in the documentation. – Triynko Jul 13 '09 at 18:46
1

I found the answer here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22785783.html

Relevant SQL Server Documentation is here:
http://msdn2.microsoft.com/en-us/library/ms189128.aspx
"When you are detaching or attaching a database, the Database Engine tries to impersonate the Windows account of the connection performing the operation to guarantee that the account has permission to access the database and log files. For mixed security accounts that use SQL Server logins, the impersonation might fail."

Also, it clearly states: "File access permissions are set during any of the following database operations: creating, attaching, detaching, modifying to add a new file, backing up, or restoring."

Obviously, I need to RTFM, haha.

Triynko
  • 3,418
  • 6
  • 31
  • 30