Home / server / Questions / 39981
Accepted
Triynko
Asked: 2009-07-14 08:49:45 +0800 CST

How can I tell what user account is being used to perform an action (in context of SQL Server)?

  • 772

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.

sql-server sql-server-2008 permissions user-management runas

3 Answers

  1. 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.

    • 2

  2. 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.

    • 1
  3. Best Answer

    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.

    • 1