"Changed Context to ..." message when editing permissions w/ VBA

Question

I have a client using three small SQL Express databases with MS Access front ends. I've created a form in each of the front end apps that allows a supervisor the ability to perform some very basic security operations. The VBA code in the form concatenates some simple TSQL strings and then executes them via an ADO connection. The strings are as follows:

1) Add new login to server:
"CREATE LOGIN [NETWORK\UserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"

2) Add user to database:
"USE MyDatabase CREATE USER [NETWORK\UserName]"

3) Grant Write Permissions:
" USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORK\UserName' "

4) Remove Write Permissions:
" USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORK\UserName' "

Everything runs perfectly for me. However, when the supervisor tries this, he gets a message that says "Changed Context to MyDatabase, Error# -2147217900, 80040e14" and the VBA code fails.

I've found two workarounds, but they have drawbacks that I'd rather not deal with

1) If I change the default database for his login to "MyDatabase1" it runs fine. But sometimes he needs to be able to perform these same operations in "MyDatabase2" or "MyDatabase3".

2) If I grant him "db_owner" status, it runs fine. However, I'd rather not hand out "db_owner" status if I don't have to

The only other idea I have to to build "EXECUTE AS.." stored procedures for each of these operations and then call those SPROC's from VBA.. Will this help? Any other ideas?

Answer 1

Any ideas at all on this? It's really getting to be an important thing for me to fix..

Answer 2

Can you grant him securityadmin database role?

Answer 3

First, The supervisor needs to be a user in all three databases.

Second, if you are using Execute AS statements you need to revert before you change databases or things get very confusing.

 

Finally, I'd try to run these commands against the SQL server itself and see what errors you get back.  Use sqlcmd or osql.

 

HTH,

 

-Steven Gott

SDE/T

SQL Server