Problem W/ Linked Table Permissions
Aug 24, 2004
This question involves SQL server as well as Access, so I hope that someone can follow.
I'm trying to give someone read and write access to a table in SQL server through a linked table scenario in Access.
I set up a new user account with datareader and datawriter permissions and made sure it went into the particular table as well. I also re-created the DSN on the user's local machine using this new login information. However, each time you open up the table after logging in using this info and try to edit a field, it gives a "Write Conflict" error and gives three choices: Save Changes, Copy to Clipboard, Drop Changes and of course, Save Changes is disabled.
We also tried logging in using the db admin account and it won't work.
Anyone have similar experiences with this? If so, is there a work-around so that you can edit data through Access?
Thanks in advance,
Colin
View 1 Replies
ADVERTISEMENT
Jun 13, 2006
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
View 3 Replies
View Related
May 7, 2007
This is a continuation from another thread in the SSIS forum. The broader issue is a Security/Permissions issue.
We are running SQL Server on a Windows 2003 server that is isolated from our main Novell network. The db admin does not want to load the Netware Client on the server for security reasons. As we continue to develop and extend our data access I am finding, as a developer, that I will need to frequently consume data and files store on the Novell network.
I did get the "net use" command working in a Job as a part of an SSIS package. This took a fair amount of research and trial and error to get working but at least it works. Now I am trying to use the same process to open a connection to Novell in the Query editor so I can setup a stored procedure that a Web service can call to open a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the net use command succeeds but does not make the conncetion availible to any other session. When I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. It works fine in a dos window opened manually. So I started looking at permissions. Whoami returns Administrator from the manual dos window. Whoami returns nt autority from the batch file. Since I have a valid Novell user/password in the net use command why does if fail? When I used impersonate it did not change the user from NT AUTHORITY/?? I can't a connection open.
My goal is to find a method to connnect and disconnect to the Novell network as needed from SQL Server as well as from IIS Web applications. My Network Admin does not have any answers for me. I have done a ton of research and so far the only thing that has worked, at least partially, is "net use." Does anyone know how to overcome this issue? I know next to nothing about networking and security and certainly not how to get Windows and Novell to play well together without the Netware Client running. I do have the Gateway for Client Services running but not logged in for the net use function. Any help would be greatly appreciated!
Cory Bonallo
Retail Services
Developer
View 6 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
Jun 7, 2007
I am trying to create a linked server in the management studio and am getting an error
"A required operation could not be completed. You must be a member of the sysadmin role to perform this operation"
I have tried giving the user rights via
GRANT ALTER ANY LINKED SERVER TO [DOMuser]
as well as adding them to the setupadmin group. No luck.
I can add the linked server via sp_addlinkedserver as the user.
Any ideas?
View 1 Replies
View Related
Oct 4, 2001
is it possible to have 1 column in a table that is the only updateable column in the table?
View 2 Replies
View Related
Feb 4, 2007
I want to set permissions on two tables...i dont want to allow delete or truncate statements to be executed on those tables. how can i do it....(sqlserver 2005)
View 1 Replies
View Related
Jun 18, 2004
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
View 4 Replies
View Related
Feb 13, 2008
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
View 5 Replies
View Related
May 19, 2004
I have always set up sql security for reporting to have select permissions on the tables to be used in the reports. I'm told by my coworkers that this is not advisable and I should only use stored procedures for reporting. I use stored procedures for all application duties, insert/update/delete etc but find with reporting it's much easier to just provide select permission to the tables. The only drawback to my method appears to me to be a user could conceivably view all data in tables while with the stored procedure one can limit what is being viewed. Anything else I'm missing? Thanks.
View 2 Replies
View Related
Mar 22, 2006
Does anybody know of a way to allow non-administrators to execute the truncate table statement?
I have developers that from time to time need to move data between their databases usinge the DTS wizard. Most of their tables have identity columns and in order retain the identity seed, they need to click on the option to enable identity insert. This option isn't available to non administrators.
View 4 Replies
View Related
Jul 12, 2000
I have a group that has select, insert, update, delete permissions and I have a user in that group that
needs permissions also to drop tables.
does that login need to be aliased to dbo - which is in public -- do I then to give puboic all the other permissions.
Is there a way to just give one login all permissions including dropping tables??
Help!!
View 1 Replies
View Related
Apr 19, 2004
Is there a way to replicate the table permissions from publisher to
subscriber? I noticed that when replication takes place, the permissions
that were set up on tables on the subscriber are wiped out. I need the permissions to be send to the subscriber automatically.
View 8 Replies
View Related
Sep 25, 2007
I have give permission to one SQL Server 2005 user account on a table in my database. i want to script that or any permission i have on table.
my question is, how to create that script in SQL server 2005. if i right click the table -> select "script table as" and select "create to" new query editor, it only creates script for creating the table and doesnt include the permission any account have on that table.....how to do that ? plz help
View 1 Replies
View Related
Aug 2, 2007
I have a list of users that I want to restrict access to tables in a database. The goal is to allow the users to use select statements on the views instead of the tables. How can this be accomplished?
View 14 Replies
View Related
Aug 11, 2000
SQL Server 7.0 (SP1)
Error:
------
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.
Thanks for any input.
View 1 Replies
View Related
May 20, 2015
Is it possible to check/notify if something (record) has changed in the linked table (from linked server) and automatically update table in local one?
View 4 Replies
View Related
Feb 7, 2008
Hi,
I have a table in my database where I want the Insert/Modify permissions to only administrator or (User X). Remaining users can just read the data. How do I set this in sqlserver 2005 database?
I can right click Table->properties->Permissions and add specific permissions to admin. But how would i deny permisssions to all others? I cannot add each login to deny permissions.
Thanks,
View 5 Replies
View Related
Mar 3, 2005
I have a trigger on an orders table. It checks against a patientmaster table to see if the sentflag is set to n or y. If it is "n" I need to push a record to a table on a separate db table. The user has permissions on the orders table. Without having the user be added and given permissions on the second db and table, what would be the best approach inside the trigger to handle this. I am using nt/sql security for this
View 3 Replies
View Related
Mar 3, 1999
I am having trouble with permissions on views and tables. I have a set of tables owned by dbo, and then a set of view owed by another user, say User1.
So I have a table dbo.Airports, and then a view User1.Airports. User1 has all permissions on dbo.Airports, and via ISQL I can select and update with no problem. MY VB application is working with User1.Airport, and it will select from User1.Airport fine, but I keep getting an error when I try to Update User1.Airport. I have many other tables set up the same way, and they all work fine - my VB app updates no problem. Any ideas?
I have dropped and recreated the table and all views, assigned all permissions, everything looks good, but...
Another strangev thing is if I use Enterprise Manager, and display permissions by user, and display all tables and views, the permissions are checked off for the view, but not the table, (which is incorrect) but if I don't display permissions for view, the permissions are checked off forv the table, which is correct. So that seems not to be working correctly.
View 1 Replies
View Related
Jul 4, 2013
I have an app which calls a SP, which in turn calls a CLR Stored Procedure.
The CLR stored procedure calls a number of different tables, using a Context connection string.
The issue is that the CLR SP requires the user to have permissions to the tables directly, instead of just permissions to the SP which was expected.
If I just give permission to the SP, then the CLR SP fails. So I then add the table permissions, and it then works.
So the question is, how do I raise security so the app does not have permissions on the tables?
View 5 Replies
View Related
Jan 15, 2008
A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
Thanks.
DanYeung
View 3 Replies
View Related
Jul 23, 2005
I have a pivot table implementation, part of which is posted below. Itreturns no errors in query analyzer, but when profiler is run, it showsthat "Error 208" is happening. I looked that up in BOL and it meansthat an object doesn't exist. This block of code below works fine on mylocal development machine, but not on our shared development serveruntil I go into the tempdb and make the user have the role db_owner.Even wierder is that when I do a select * from ##pivot there is noerror, but if I specify the single column name (pivot) i.e. selectpivot from ##pivot, it takes the error...Obviously this is a rights issue, but is there any way around thisother than making the user owner of tempdb??declare @select varchar(8000), @PackageId intset @PackageId = 10set @select = 'selectCompany = COALESCE(Users.Company, Contact.Company, ''''),SubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactIdFROMSubContractPackageVendorLEFT JOIN SubContractPackage ON SubContractPackageVendor.PackageId =SubContractPackage.IdLEFT JOIN Users ON UserOrContactType = ''User'' AND UserOrContactId =Users.UserIdLEFT JOIN UserRoles ON UserOrContactType = ''User'' ANDUserRoles.UserId = Users.UserId AND UserRoles.ProjectId =SubContractPackage.ProjectIdLEFT JOIN Role ON Role.RoleId = UserRoles.RoleIdLEFT JOIN Contact ON UserOrContactType = ''Contact'' ANDUserOrContactId = Contact.IdLEFT JOIN SubContractLineItem ONSubContractLineItem.RefType = ''Package'' ANDSubContractLineItem.RefId = SubContractPackageVendor.PackageIdLEFT JOIN SubContractLineItem as SubContractPackageVendorItem ONSubContractPackageVendorItem.RefType = ''PackageVendor'' ANDSubContractPackageVendorItem.RefId = SubContractPackageVendor.Id ANDSubContractPackageVendorItem.RefSubId = SubContractLineItem.IdWhereSubContractPackageVendor.PackageId = ' + CAST(@PackageId as varchar)+ 'GROUP BYSubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded, Users.Company, Contact.Company,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactId'--print @sqldeclare @sumfunc varchar(100),@pivot varchar(100),@table varchar(100),@FieldPrefix varchar(5),@TotalFieldName varchar(50),@PivotFieldFilter varchar(1000)select@sumfunc ='Sum(isnull(SubContractPackageVendorItem.Total,0) )' ,@pivot ='SubContractLineItem.Category' ,@table ='SubContractLineItem' ,@FieldPrefix='~' ,@TotalFieldName = 'Total' ,@PivotFieldFilter = ' AND RefType=''Package'' AND RefId=' +CAST(@PackageId as varchar)set nocount onDECLARE @sql varchar(8000), @delim varchar(1), @TotalSql varchar(8000)SET NOCOUNT ONSET ANSI_WARNINGS OFFEXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + 'WHERE 1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' +@table + ' WHERE '+ @pivot + ' Is Not Null ' + @PivotFieldFilter)SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )SELECT @delim=CASE Sign( CharIndex('char',data_type)+CharIndex('date', data_type) )WHEN 0 THEN '' ELSE '''' ENDFROM tempdb.information_schema.columnsWHERE table_name='##pivot' AND column_name='pivot'select * from ##pivotDROP TABLE ##pivot
View 6 Replies
View Related
Jul 20, 2005
Hi, is there any way that I can automate granting user permissions totables/ stored procedures in SQL server 2000?I have a whole bunch of tables and rather than having to right click eachtable/ then permissions in Enterprise manager I would like to be able toiterate through each table object in a database and grant the relevantpermissions.... Same with stored procedures.Is this possible?? If so, how can I do itThanks in advanceMark
View 2 Replies
View Related
Sep 25, 2007
Just out of curiosity, could someone point me towards a listing of the user permissions for the MSDB table? I have looked through BOL and on the internet and cannot find a good listing. An example would be something like...
dts_admin: <dts_admin description>
Thanks in advance.
-Kyle
View 1 Replies
View Related
May 28, 2015
We have a process that uses the following method to move data quickly:
TableA = 600million records
TableB = 0 records
To "move" data from TableA to TableB
Rename TableA --> TableA_HOLD
Rename TableB --> TableA
Rename TableA_HOLD --> TableB
The problem with this is that after every rename, permission to the TableB is lost so we have to execute a statement to grant permission back to TableB after the process is complete.
My question is why is this necessary? Is the permission tied to the table in such a way that it can't use the name only?
View 9 Replies
View Related
May 20, 2008
Hi
What permissions do I need to set so that a user can change tables, views and procedures?
View 7 Replies
View Related
Jan 10, 2008
I want our developers to be able to alter procs owned by the dbo schema, but for data modelling reasons, I want to exclude them from creating or altering any tables in the dbo schema. I can't seem to figure out how to do this, is there a way?
Thanks,
View 1 Replies
View Related
Jan 3, 2008
Nice simple problem, trying to set the guest user with SQLCMD to have select permissions on an object:
-- Code below
-- Line below should read "colon"setvar MAD guest
: setvar MAD guest
SELECT $(MAD)
GRANT SELECT ON OBJECT::dbo.ErrorLog TO $(MAD)
Unfortunatly I keep getting the following error being thrown:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'guest'.
Any ideas?
Thanks,
d
View 1 Replies
View Related
May 4, 2007
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
View 7 Replies
View Related
Jun 6, 2007
Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.
there is one function in SQL : €œSELECT * FROM fn_my_permissions('TableName', 'OBJECT')€?
This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.
Is tehre any way to acheive that?
-Mani
View 1 Replies
View Related
Nov 16, 2006
How do I allow a user (or group of users) permission to create/drop a table?
I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well?
'GRANT DROP TABLE TO username' doesn't work?
and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.
thanks
View 3 Replies
View Related
Jun 2, 2007
I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.
Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.
View 10 Replies
View Related
