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...
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?
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?
I am using a Microsoft Access ADP to get to data on SQL Server 2000. I would like to protect the base tables from being edited directly, but allow the views and SPs to handle all the work of getting data in and out. From what I have read in numerous articles and boods, I should be able to grant permissions just on the Views and NOT on the tables. However, the ONLY way I can make my views 'updatable' is by graniting UPDATE permissions on the TABLE! Worse yet, if I DENY permissions to UPDATE, INSERT, and DELETE in the view, but allow them in the table, the view allows the updates anyway, apparently not looking at the fact that it should be DENIED.
:confused:
I have 500 tables in my Db.In those some tables name starts with abc (abc_emp,abc_dept) and rest of the tables name start with xyz(xyz_emp,xyz_transactions).I wanted to give select,insert,update and delete permissions for an user on the tables which starts with abc.
How can i do that in a much easier and sophisticated way.
Thanks.
We are attempting to implement security on top of a shrink-wrapped softwarepackage and are trying to get row-level security. Here's the scenario:1. Table dbo.BOOK contains all the information about books in everydepartment.2. There are a large number of developed reports that run queries like"select * from BOOK..."3. We wish to have each Department only be able to see their books - withoutchanging the existing reports.Our thought was to create a series of views:create view Dept1.BOOK asselect * from BOOK where Dept=1....and then create Roles for each Dept. We'd then remove rights to dbo.BOOKand grant rights to DeptN.BOOK as appropriate for each role. We startedtesting this and seemed to get it working, but are now having problems. Isthis possible? Is there another, better solution?Thanks!
View 5 Replies View RelatedHello,I need some help with implenting the following:I recently migrated from access to sql server and i now i want to usemaintainable permissions on my tables, views, etc. The access database willserve as a front-end.I've created for testing purposes an testaccount with only a public role toaccess to my database.Now the hard part is when i want users to select and manipulate the datathrough views and stored procedures.I want only permissions set on views andstored procedures. The reason for this is because i don't want users to getthe data directly from tables by means of linking or importing them toaccessor other databases. Only views and stored procedures can be used.Unfortunelately it doesn't work how i wanted to. When i open a view which islinked in access as a table, i'm getting a message that the underlying tablehas not the appropiate permissions.Now there should be a way to apply a maintainable security, so if i couldhave some advice and maybe an example on this matter i would be verythankful.
View 3 Replies View RelatedA client recently changed his sort order and character set definitions. He is now unable to restore any of his tables. Prior to making the change he carried out the following steps;
1. Printed out the schema(scripts) for each database,
2. BCP`d all data out of SQL.
He the proceeded to make the change (via SQL Setup, rebuild master database option), then he recreated each device (data and log), recreated each database (data and log), ran each schema to rebuild each database (structure, views, etc) and then BCP`d all data back into databases.
He has been doing a full backup everyday since. He needs to restore a table but when he does so SQL reports that the schema is different between source and destination, no changes have been made.
When you create a stored procedure and give the user execute permission, you don't need to give the user select permission on the table used in the stored procedure.
If one of the tables in the stored procedure is a synonym referencing a table in another database, and the user is already in the other database, you get a select permission denied on that table and I could only get it to work if I gave the user select permission on that table.
Is there a way around that, since I hate giving select permissions on tables?
Thank you,
Wissam
Does anyone know what permissions are required to run bulk insert to a temp table?
I've got a procedure that creates a temp table and runs bulk insert on it. Only problem is that it seems that only the dbo can run it. Anyone else gets the following error:
"The current user is not the database or object owner of table '#bulk'. Cannot perform SET operation."
Alternatively, does anyone know how to submit a scheduled job as a different user?
I've got my import system set up to create a scheduled job that kicks off right away. The job runs the 1st step which includes a bulk insert. The second step checks if the first step completely failed or not. Works great except that when the user runs the submission procedure, it comes back with that error.
Microsoft's brain-dead bulk insert command....aaargh. Every method around it's design flaws is blocked by another design flaw.
I posted a message regarding a problem I'm having with SQL server 7 nottaking any notice of the permissions that I'm setting up on my databasetables (see thread "SQL Server 7 ignores user permissions" started on10/10/2003). I did get one response to this original message, butunfortunately this did not resolve my problem. Can anyone else shed anylight on this issue for me please?Thanks,Jon Ley.
View 1 Replies View RelatedAs a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will
Go into SQL Server Management Studio 2008Open the database
Locate either the table or the view
Right click on permissions.
Add in the role or user that's needed
Grant them Select permissions
This allows the report in CRM to access the database and this works.
The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.
I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.
Hi,
I want to know how to copy tables and data from one database to antoher database including table permissions. Presently i am using Integrity security services. Is it having any option in Integration services or sqlserver 2005.
Thanks
what are the minimum permissions to allow a user to view, create, & drop tables within a DB (SQL 2008)?
View 4 Replies View Related
I am setting my error tables.What i want to do is.If i find any data within my error tables i want an email to be sent to me.
How can i achieve this??
Please let me know
We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated.
View 5 Replies View RelatedHiI have two databases: Customers and Operations. In Customers database I havemade a view based on a few tables from both Customers and Operations (leftjoin - customers without any operations). In the same database (Customers) Ihave created a stored procedure based on the view. Finally I'd like to giveto some users permission only to exec the stored procedure.Have I to add the users to Customers? If yes, please describe me how tolimit the users privileges only to execution the stored procedure (no rightsto open tables or view from Customers).Regards,GrzegorzPs. I had sent the post on microsoft.public.sqlserver.security, but I had noanswer.
View 5 Replies View Related
Hi ,
I am a newbie and i need to provide access for developer for him to use bulk insert ... on temp tables.
what permission do i need to provide the developer i cannot provide bulkadmin permission to him what are the other ways to provide him the access.
Please help me with this.
Hi!
Is it possible to generate a script, that extracts every bit of data it can, from tables which suffers from latch errors;
DBCC CHECKDB ->
Msg 7985, Level 16, State 2, Line 1
System table pre-checks: Object ID 4. Could not read and latch page (1:403) with latch type SH. Check statement terminated due to unrepairable error.
So bad news on that one. There are no backup available that I know off, so all bets are off on that part of the recovery process.
The "good news" part of the problem, is that it is possible to extract some data out of the table, before SQL server 2005 returns the error. So what I want to do, is get every bit out I can, doing it somehow automatically, so I don't have to run bcp on 180 tables :).
Presumeably, this is quite possible, but how?
NB. If I do a complete basic SELECT * FROM tbl1, it returns data, then errors out, but what about the data after the last row SELECT returns? Is it possible, that there might be data in the table that are recoverable after the 'broken row(s)'?
I'm writing an application using VB 6.0 as the front-end GUI, and theMSDE version of SQL Server as the back-end (it's a program for areally small # of users --- less then 3-4).I'm trying to determine, through the Visual Basic interface, thepermissions of each user that's using the application on his/hermachine.For example, let's say I'm user "Michael" that's sitting down at mymachine using the app. I've written. The security for logging intoSQL Server will be setup using Windows Security (Trusted Connection)as opposed to Windows & SQL Server security. When Michael accesses aparticular form in the VB 6.0 GUI, I want to run some code thatautomatically checks Michael's permission levels on the underlyingtable (actually, a stored procedure supplying the data from the table)that supplies the data to the form he's looking at and then give himsome feedback on the form as to what type of permissions he has whilehe's browsing through the data shown in the form.For example, Michael opens a particular form, code in the backgroundis run to identify that this is Michael accessing the form, the codereturns a value that identifies what type of permissions he has on thedata in the form, and a text box on the form informs Michael (forexample) that he only has read-only permissions to the data he isviewing and cannot edit any of the data.As another example, user Karen sits down at her computer, logs intothe application, opens the same form that Michael just opened, thecode is run in VB to detect the level of permissions she has on thedata being displayed in the form, and the text box on the form informsher that she has editing permissions on the data in the underlyingtable.Etc...If anyone can post an example of the code they use in accomplishingthis task in an application they've written, I'd really appreciate apoint in the right direction or a real-world example that's beenimplemented by one of you. I've written several apps. thus far usingMSDE as the back-end, but the previous apps. I've written were forclients that didn't care about restricting access to theapplication... everyone could pretty much use the application as theydesired and do anything they desired to the data.The current client I'm writing the app. mentioned here for wants tohave security in place to where various users access the applicationwith various levels of permissions to do stuff (or *not* do stuff) tothe data in the application.Thanks very much in advance for any assistance / code provided!Sincerely,Brad McCollumJoin Bytes!
View 1 Replies View RelatedI€™m trying to copy data from production to my local machine using the SQL Server 2005 import and export wizard. It works fine if I select a small number of tables but throws errors
When there€™s a lot of tables. Have you ever experienced problems using it? Is there a better way to transfer the data?
the data source is SQL Server 2000 and the target is 2005. I have the optimize for many tables and transaction options selected
Here€™s the errors I get
Execute the transfer with the TransferProvider. (Error)
Messages
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
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 RelatedStarwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
Is there a way to produce a query to look at when and who changed database objects last?
View 1 Replies View RelatedI have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.
'CONTACTS' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.
will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?
I have an IDENTITY value in a table that was incorrectly created, and needs to be modified. Can this be accomplished?
Thanks.
hi all
im really have a problem in my project.
i have server and client side each side contain SQL Server DB.
and i have excel file on the server side this excel file conected with another server, this file changed data in continuosly each less than 1 sec by data feed.
now i need to read each changed data cell from this file to save it on server DB and Client DB (just changed data).
my problem :
u know changed event is not fired when change cell by data feed or not edit manually, just calc event is rised . but calc event do not specify the changed cell range (address).
so i do this to know changed cell range:
when run the program saved all excel tabel into SQL server table.
and then check row by row between excel and sql if any change , when i get any change, i update the excel row insted this DB row.
and rise event to send this row to client by socket over internet to update the row in client side too.
but i tell u that the excel file updated each less than 1 sec, and i noted that many changed excel data missed until checked row by row for whole excel sheet with DB tabel and updated change.
this is my problem ( please help me as soon as posible coz i have dead line to Delivered this program)
and if u recomended me for another techneque to be easy or quickly to solve this problem i will thaks so much for u)
thank u.
AL-Khateeb
Maybe somebody has asked this question, but I could not find it....
I used SSIS to transfer data from Oracle database to SQL Server 2005 (with Microsoft OLE DB Provider for Oracle). It worked fine with the TNS alias name said oracle1. Then I changed the TNS alias name to oracle2. I could connect to Oracle database with oracle2 using Oracle's tool. But SSIS could not find (or solve) oracle2, if I changed the name back to oracle1 in SSIS, then SSIS worked.
Any idea?
Thanks!
Hello,
I have developed a report based on a stored procedure which will generate an excel file. There is a requirement that this file needs to be sent to an external domain with the name changed accordingly i.e., a date is to be suffixed to the file after the name has been changed based on their naming convention.
I would appreciate, if anybody has done so and can give me some steps for me to work out.
TIA
I have a page that uses an SQLDataSource web control to populate a GridView control. The SQLDataSource control uses three parameters, one of which is a text string (varchar(50)). I get timeout expired exceptions when I run the page with two specific string values, yet when I run the stored procedure in SqlSvr Management Studio, the procedure runs in 5 seconds and under 1 seconds. Being rnew to ASP.NET 2.0 and .Net in general, I have no idea where this lag is occurring. I would like to know two things:
1. What is causing the lag that causes the timeout in ASP.NET 2.0.
2. What can be done to adjust the timeout period SQLSVR, IIS, ASP.NET).
The underlying query is running as desired (considering the data it has to chew on). For all other values, data is returned almost instantanious. I can provide a copy of the query id need be.
Truly appreciate any insight you can give me!
i changed my state table from char 20 to varchar 20 and it still fills it with empty space at the end of the statename
I tried entering it with the trim command still no good
i looked a the sql command made in vs and it says that the variables are varchar
is there something i am missing with varchar
does it only work properly if it is over 50 or does it not change completely when you change from char to varchar