0

I've used the technique in this question to impersonate the Windows User who has admin rights over a database. I thought that this would allow the process to alter tables etc (which if I'm logged on as that person I'm able to). My connection string uses IntegratedSecurity and after I prompt the user for db admin username & password I impersonate that user and then reconnect to the database. I thought this would allow me to create tables etc, but no ...

Is there something else I need to do to give my program (temporary) admin rights to my database?

I've tried following the impersonation with EXECUTE AS LOGIN = @user, but it wasn't happy - do I need to supply a password to SQL as well to make that work, perhaps?

Community
  • 1
  • 1
noelicus
  • 14,468
  • 3
  • 92
  • 111

2 Answers2

0

You can either add a role to the logins you want to use, or grant specific permissions. The second is typically the preferred method, as you can be much more granular in the amount of access provided to the account.

Community
  • 1
  • 1
dmarietta
  • 1,940
  • 3
  • 25
  • 32
  • I don't want to give the user any permissions - they have a restricted role for a reason! I want the administrator to be able to sit at the machine and type in his/her credentials that will be used temporarily to update the database. – noelicus Dec 18 '13 at 15:39
  • 1
    Then you would have to run the APPLICATION in a different user context. – dmarietta Dec 18 '13 at 15:49
  • So impersonation isn't enough, then? – noelicus Dec 18 '13 at 15:51
  • Correct, as the SQL Server only can see the login context for the logged in account. – dmarietta Dec 18 '13 at 16:07
0

You may well be having a problem with the Connection Pool. When a process closes a connection, the connection is not in fact closed, but goes to the processes Connection Pool. The next time that process tries to open a connection it will get one from the pool if that connection in the pool has the same connection string as the requested new connection. Thus you likely aren't getting a new connection at all, but an already opened one with the auths of the original opener.

The only way around this I can see is to use a different connection string for the admin user: add or remove a clause that makes no difference (e.g. 'MARS Connection=true' or something). That should work.

Cheers -

simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • Tried that but it now says the server is not accessible!! - seems to hate the new user ... "SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified" – noelicus Dec 18 '13 at 15:59
  • What does the changes connection string look like? – simon at rcl Dec 18 '13 at 16:02
  • I changed the connection timeout to 17 – noelicus Dec 18 '13 at 16:04
  • `"Data Source=localhost\\SQLEXPRESS;Initial Catalog=autumn;Integrated Security=True;Connection Timeout=15"` to `"Data Source=localhost\\SQLEXPRESS;Initial Catalog=autumn;Integrated Security=True;Connection Timeout=17"` – noelicus Dec 18 '13 at 16:06
  • The only thing I can think of is that the new user doesn't have access to the folder where the mdb is stored. If that's all you've changed then I can;t think of anything else (I don't use SQLExpress much I'm afraid). – simon at rcl Dec 18 '13 at 16:10