Changed Context To ... Message When Editing Permissions W/ VBA
Feb 15, 2008
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 [NETWORKUserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"
2) Add user to database:
"USE MyDatabase CREATE USER [NETWORKUserName]"
3) Grant Write Permissions:
" USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORKUserName' "
4) Remove Write Permissions:
" USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORKUserName' "
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?
View 3 Replies
ADVERTISEMENT
Oct 25, 2007
I have a client using a small SQL Express database with an Access front end.
I've created a form in the front end that allows a supervisor the ability to perform two very basic security operations. 1) Adding a new user, and 2) granting/removing write privileges. The code behind the form uses VBA to build some T-SQL strings and then executes them via an ADO connection.
The strings are as follows:
1) Add new login to server:
"CREATE LOGIN [NETWORKUserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"
2) Add user to database:
"USE MyDatabase CREATE USER [NETWORKUserName]"
3) Grant Write Permissions:
" USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORKUserName' "
4) Remove Write Permissions:
" USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORKUserName' "
I have full privileges over the server & database, and everything runs perfectly.
However, when the supervisor - whose login includes security admin privileges - tries this, he gets a message that says "Changed Context to MyDatabase, Error# -2147217900, 80040e14" and the code fails.
If I grant him "db_owner" status, the message does not come up and everything runs perfectly.
I'm not a security guru or anything, but I suspect that it's not the greatest idea to hand out db_owner privileges.
I ran a search, and the best I could come up with were a couple of KB articles:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;197459
http://support.microsoft.com/default.aspx?scid=KB;EN-US;143339
I'm not even sure these articles are talking about what's happening..
Any ideas?
View 4 Replies
View Related
Jul 9, 2002
I am getting the following error while trying to update a SQL Server 7.0 table thru' MS Access('97 version). It was working fine until now. This problem started happening after re-linking the table to MS Access as new columns have been added on the SQL Server table. We tried re-starting the MS Access,re-linking the table again,giving all the permissions to the user on the table etc. Also, no other user is accessing the table at the same time..Any help?
Error message in MS Access:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
thanks,
Mike.
View 1 Replies
View Related
Jan 13, 2005
I am in a bind here..
I am getting the message (after do any sort of query) Database context changed to <dbname> I am using PHP as the p language.
Does anyone have any clue on why it is doing that..
Recently my host upgraded there PHP to version 4.3.10 and before the upgrade the queries were working fine.
Im on a windows server that runs PHP and im using a MSSQL database.
I've checked the bug reports on PHP and didn't come up with anything as well as googling the error..
IF you have any insite on this problem please let me know.. It may be a stupid error that I am overlooking on my part.
Thanks in advance
View 1 Replies
View Related
Mar 2, 2008
Hi try to connect with driver {SQL Sever Client} but i can't
I'm have SQL Server 2005 Express SP2
Error connecting to the ODBC server:[Microsoft][SQL Native Client][SQL Server] Changed database context to..
Please Help me
Thanks
View 10 Replies
View Related
Jul 7, 2006
Hi guys
Am i able to deny the SA account access to a specific database?
thanks in advance for your help.
TheGing
View 4 Replies
View Related
Oct 3, 2007
Hai,
I'm having Windows XP SP2 version and I have installed SQL server 2005. Whenever I'm opening SQL server management studio in Windows Authentication Mode I get a pop message indicating that "SSPI context error message". What may be the problem and how to sort it out?
Narayanamoorthy
View 1 Replies
View Related
Oct 5, 2007
Hai,
I usually connect SQL Server 2005 using Windows authentication mode (trusted connection). I have one server to which four client machines are connected and all the clients are windows XP SP2. The SQL Server 2005 I have installed is Standard Edition. When I'm connecting I get a pop message indicating that "SSPI context error message". I have seen the Troubleshooting Article ID : 811889. I was'nt able to resolve it. What may be the problem and how to sort it out ?
Narayanamoorthy
View 3 Replies
View Related
Jul 25, 2007
Hai,
I usually connect SQL Server 2005 using Windows authentication mode (trusted connection). I have one server to which four client machines are connected and all the clients are windows XP SP2. The SQL Server 2005 I have installed is Standard Edition. When I'm connecting I get a pop message indicating that "SSPI context error message". I have seen the Troubleshooting Article ID : 811889. I was'nt able to resolve it. What may be the problem and how to sort it out ?
View 3 Replies
View Related
Sep 20, 2001
Recently some permissions were changed on the database tables and now the following message appears when a script is run to update the DB.
Msg 547, Level 16, State 1, Server SQL, Line 2
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'fk_educ_id'. The conflict occurred in database '12345', table
'educ_matters', column 'educ_id'.
The statement has been terminated.
(0 rows affected)
Thanks in advance...
View 1 Replies
View Related
Jul 23, 2007
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
View 3 Replies
View Related
Aug 8, 2007
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
View 5 Replies
View Related
Aug 8, 2007
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
View 1 Replies
View Related
Aug 2, 2006
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies
View Related
Feb 2, 2001
If I have a *.dts file, and want to change the originating server database from SERVERA DB_A to SERVERB DB_B, is there an easy method to do this, besides editing the file in the GUI form?
View 1 Replies
View Related
Jun 16, 2007
I need help transferring a SQL database. I currently have my website database on a SQL 4.0.27 (vdeck) and want to transfer it to a new server (VPS) which has SQL 4.1.2
I have imported the database to my desktop computer. It will not let me import to the new server. I have to edit/reconfigurate.
Any suggestions, comments, advice, help would be greatly appreciated.
Ed
ed.sistrunk@sistrunk.net
View 4 Replies
View Related
Nov 4, 2003
Can I edit database (Tables, SPs, Views,...) with ADO.NET?
View 3 Replies
View Related
Feb 16, 2005
hey guys,
is it possible to add or edit columns once there is data in the tables??
not the data in the columns but the columns themselves..
for example lets say i have a table with 4 columns and for some reason or
another, i want to add a 5th or 6th column after data has already been entered,
like a few months down the line..
View 2 Replies
View Related
Mar 9, 2005
I have scheduled a T-SQL job that runs every morning using Enterprise Manager. Now I want to change the SELECT and UPDATE statements that this job runs, but I can't find anywhere to edit a job that has already been scheduled. Any help would be appreciated.
Thanks
View 1 Replies
View Related
May 6, 2008
I keep getting "cannot update a timestamp column"!! How can I change the timestamp to all zeros? or can I not do that?
JOhn
View 4 Replies
View Related
Nov 16, 2007
Hi
I want to Enable Editing in the GridView
but there is a problem
the GridView take its data from 2 tables and I do this by the
build query
so I can't choose the " advance " button and check for enable update and delete statements
and when I try to build the update query in the update tap
I don't know what to write in the "new value" column
so how can enable editing in the GridView that take from
2 tables???????
please help me
and thanks
View 3 Replies
View Related
Nov 17, 2006
Hello,
I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.
// Create an empty request message
string Msg = "Hello";
MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));
Message request = new Message("Request", body);
Thanks,
Scott Allison...
View 1 Replies
View Related
Feb 4, 2007
can someone help me why it produces an error....
error is: ERROR 22001 Microsoft ODBC SQL Server Driver SQL Server STRING or data of BINARY was cut short. ERROR 01000 Microsoft ODBC SQL Server Driver SQL Server statement was ended.
This is my code here for editing a record....
Protected Sub Button_save2_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_save2.Click '||||| Create string connection Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft® Visual Studio® 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes" '||||| Create connection object Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)
'||||| Open connection MyConn.Open()
'|||| Create odbcCommand object Dim Update_record As New Odbc.OdbcCommand("UPDATE TM0001 SET TM0001.syain_name = ?, TM0001.syain_pass = ?, TM0001.office_id = ?, TM0001.birth_date = ?, TM0001.empl_date = ?, TM0001.user_iden = ? ", MyConn)
Dim hireYear As String Dim hireMonth As String Dim hireDay As String Dim date_hire As String
hireYear = DropDownList_hire_yr.Text hireMonth = DropDownList_hire_mo.Text hireDay = DropDownList_hire_day.Text
date_hire = hireYear + "/" + hireMonth + "/" + hireDay
'|||| Add command parameters Update_record.Parameters.Add("@P1", OdbcType.Char, 8).Value = TextBox_id.Text Update_record.Parameters.Add("@P2", OdbcType.Char, 20).Value = TextBox_name.Text Update_record.Parameters.Add("@P3", OdbcType.Char, 20).Value = TextBox_pswd.Text Update_record.Parameters.Add("@P3", OdbcType.Char, 40).Value = DropDownList_office.SelectedValue Update_record.Parameters.Add("@P3", OdbcType.Char, 2).Value = date_hire Update_record.Parameters.Add("@P3", OdbcType.Char, 10).Value = TextBox_bday.Text Update_record.Parameters.Add("@P3", OdbcType.Char, 1).Value = DropDownList_iden.SelectedValue
'|||| Execute command Update_record.ExecuteNonQuery()
'|||| Close connection MyConn.Close() End SubEnd Class
View 3 Replies
View Related
Feb 20, 2004
Hi,
I want to get (SELECT??) data from a db (SQL), edit the data (+1) and then update (UPDATE??) the table with the edited value. How do I get the value from the db and then edit it and then update the field in the db?? Does someone has an example so I get on the way??
Thanks,
Roel
View 9 Replies
View Related
Aug 15, 2005
Is there anyway possible to edit table design with data already entered in the table.If not, is there anyway I can cut and paste the info back in. Ive tried importing to access and then back to SQL, however, when i tried to view my table design in ASP.NET webmatrix, it gave me an error.
View 1 Replies
View Related
Mar 18, 2002
I've just created a new maintenance plan (sql2k sp2) and attempted to re-enter it to make a change. right click - choose properities (or double click)...I see the dialog box 'flash' very briefly but it's not making itself available for editing.
I've deleted and recreated the plan ... same result.
I _should be able to edit it, shouldn't I?
thankx
--steve...
View 1 Replies
View Related
Jan 14, 2008
I have a SQL 2000 SP4 server running on Windows 2003 SP2. About 6 months ago I started experiencing problems editing existing DTS packages. At that time it was just 1 package. (I tested that I could edit all others but this one package the first time I experienced the problem.)
But now it seems like anytime we experience import problems, I cannot edit the package so that I can verify that the import file is good. (So far the initial problem is always with the import file and once we figure out what that is and resolve it the package runs ok again.)
The problem is I'm planning on migrating to SQL 2005 and since I can't import DTS packages, I'm going to have to rewrite them as Integration Service Projects. If I can't edit the workflow tasks in DTS, it will be time consuming figuring out what the actual import file is. Also, most of these imports are coming from our AS400 which we are phasing out so I have to rewrite these to work with the new ERP package we are going to as we bring on each division.
I have not installed any post SQL 2000 SP4 hotfixes. So if anyone is aware of a hotfix that resolves this issue, please let me know.
View 14 Replies
View Related
Apr 8, 2004
Hi to all,
We have an application that used Ms-Access for its backend.
We wanted to move up to MSDE so I wrote a conversion utility that creates the SQL database, copies all the data from the Access database, creates all the indexes and relationships.
Now, the application used a lot of sql statements which would have to be rewritten for SQL Server so we decided to create a ms-access database with linked tables to the SQL server (ODBC). Don't tell me not to do that because I have no control over it :).
The weird thing happening now is that:
All the records that were moved from access are fine.
New records can be added fine.
However, when trying to edit/delete the new records, I get an error saying that:
Write Conflict.
I can't change the record because it has been changed by another user since I started editing it.
Any ideas?
Thanks
View 2 Replies
View Related
Apr 28, 2006
I've recently been upgraded to Office 2003 and primaraly use Access to edit SQL stored procedures on a SQL 2000 box.
It seems though from first glance, things have gotten worse since Access 2000.
No more colour coding for example and it also opens some in a view type mode.
Any recomendations on what I can use that would allow me to edit the SQL while updating it on the server and pull down data if needed?
Someone recommened Visual Studio but I'm unsure of which version to use.
View 6 Replies
View Related
Feb 29, 2008
Please direct me towards good documentation about updating, inserting and deleting data using views involving multiple tables.
Thanks
View 2 Replies
View Related
Jul 20, 2005
I have an SP which I use to insert into a table data from another. Forexample...INSERT INTO myWorkTable(Field1, Field2)SELECTmyField1, myField2FROMmyNewTableIt's a pretty straight forward sp. Now, the data that gets insertedseems to have remained in a state that stops me updating it later. TheSP is called by one app which is then closed and not used again untilthe following month. The sp shouldn't be used by anything else.I have another application which goes to the table (in my examplemyWorkTable) and is supposed to open and edit specific records whichit does correctly navigate to (a few days after the sp). It fails andI get an error message stating that the record is being edited byanother user. This record should not be opened by any other process.If I export the data and re-import it into the table, it worksperfectly fine.The code used to edit the table is fine and has worked for a couple ofyears and I'm happy with it. The only change I have made is to use anSP to import the data instead of an old method using Access 2000. Thefunctionality is the same, even though the connection is different.The Delphi app uses BDE.Inserting directly through SQL works as I would have expected and Ican edit the record as I need.I'm guessing that Delphi (which I use for the app) is somehow notfreeing up the data locked by the SP when it finished calling the SP,even when it is closed. However, it seems a little bizarre as I wouldexpect it to free up the data affected by the SP once it has finished(maybe this is the wrong assumption).So, my question is, can an SP lock records in this way ? If so,how/why and can I stop this once finished, or is there a way ofchecking / resetting records which were locked up and should no longerbe this way. I can't see anything obvious which shows the records arelocked, but am willing to delve further.ThanksRyan
View 2 Replies
View Related
Jul 20, 2005
I have some software (written in Delphi 5) which has been working forseveral months without a problem.I have been given a copy of the database on our development server(SQL 7) and have pointed the software to this. So, nothing has changedwith the software. In theory the servers should be the same.However, if I try to make a small change to any record (as I would onthe 'live' system), I get an error stating that another user isediting the record. Now, I'm the only person with access to thisserver. So am I stopping myself I wonder ?If I create an ODBC connection to the database through Access 2000, Iget the same error. I feel I can reasonably assume that the softwareis not at fault.I'm sure it's something SQL based, whether it's an error orconfiguration difference, but I'm stuck.I've checked who is locking/blocking, and yes it will show me ashaving the record open, but this is me attempting to edit the recordso I would expect this.I can edit the table directly in SQL, but it takes 30 seconds or more,freezes EM and then allows the change. Table info below :if exists (select * from sysobjects where id =object_id(N'[dbo].[PostReceived]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[PostReceived]GOCREATE TABLE [dbo].[PostReceived] ([PostID] [int] IDENTITY (1, 1) NOT NULL ,[Type] [varchar] (100) NULL ,[ClientsName] [varchar] (100) NULL ,[DateReceived] [datetime] NULL ,[EnteredBy] [varchar] (100) NULL ,[AssignedTo] [varchar] (100) NULL ,[DateAssignedTo] [datetime] NULL ,[Adviser] [varchar] (100) NULL ,[TargetDate] [datetime] NULL ,[CompletionDate] [datetime] NULL ,[Completed] [bit] NULL ,[KeyAccount] [varchar] (100) NULL ,[Notes] [text] NULL ,[Specific1] [varchar] (20) NULL ,[Specific2] [varchar] (20) NULL ,[Specific3] [varchar] (20) NULL ,[Specific4] [varchar] (20) NULL ,[Specific5] [varchar] (20) NULL ,[ToDelete] [bit] NULL ,[EnterUser] [varchar] (20) NULL ,[Returned] [bit] NULL ,[ReturnDate] [datetime] NULL ,[ReturnReason] [varchar] (87) NULL ,[PrintAdviser] [bit] NULL ,[EmailAdviser] [bit] NULL ,[EmailSM] [bit] NULL ,[EmailRegionManager] [bit] NULL ,[ReturnText] [varchar] (150) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO(Yes I know about using Identity in the key, but it's on my list tochange before anyone says anything).
View 5 Replies
View Related