.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Securing access to specific tables for Excel reporting

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

I am relatively new to SQL Server 2005 and inherited it as configured.   What I want to do is improve reporting capabilties to users through a tool they are familiar with, Excel 2003.   As admin, this is not a problem and I can report on all the tables.   For a user, I want to restrict which tables a user has access to.   Not sure how.  I have read some documentation, security best practices and lots of Googling.

The SQL Server is configured with a number of databases with each having lots of tables.  The one database I need to use has the tables the user needs but also a lot of tables he should not be accessing.  They all share the same schema.  I want to restrict the tables he can see when importing the data into Excel.    Not sure how and am hoping someone can provide some constructive suggestions, whether it can be done at all, or guidance as to where I can find the answer.   I've also thought of using a view but am not sure nor can I find where I can it make it avaible through Excel.

Any advise will be much appreciated.    I hope this makes sense.

 




View Complete Post


More Related Resource Links

Getting list of users reporting from specific Manager from Active Directory

  

Dear All,


I am have requirement like displaying all the user information reporting to specific manager from Active Directory.


Please help me regarding this.


Regards

Prakash


InfoPath and Access - two parent tables and one child table

  

Hi there,

I'm trying to link an InfoPath form to an Access database, I want to connect the InfoPath form to 3 tables in the Access database but InfoPath will only recognise parent-child relationships in a series (e.g. the "Company" table is the parent to the "Customer" table, which is again parent to the "Orders" table)

I need to have two parent tables and one child table, though (e.g. "Customer" is parent to "Orders", but "Inventory_Item" is also parent to "Orders"). Is there any way to establish this in InfoPath? I'm using Windows XP, InfoPath 2007 and Access 2007.

Cheers, Patrick


Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

Linked Server to access Excel 2007

  
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???

No Access to Reporting Services Manager Website

  
Hi, I am trying to  access the report manager website with http://localhost:8080/Reports. I know that this was working in the past with the Administrator user. Now I get the login Form repeatedly and I have no access with any user. With the administrator user I can access the reporting services in Management Studio but not the website. Please help....

Reporting Services and Sharepoint Integration - Sharepoint Alternate Access Mappings (Zones)

  
I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone: System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. Is this a limitation of the SQLRS-WSS3 integration or is there a workaround? Many thanks to anyone who can help!

Importing Access 2010 tables to SQL Server 2008 R2

  
I'm trying to import a series of Access 2010 tables to Sql Server 2008 R2.  The Access import drivers are for *.mdb (which if I recall was the file extension for Access when I was a kid, and don't recognize the .accdb file extention).  Similarly, the Excel driver is for Excel 2003.  Isn't there a driver and method to import directly to SQL 2008 from Access 2010? SQL is installed on my server, but Access is not installed on the server.  When I copy the file onto the server and try and open it directly into SQL, I get a 'no editor installed' error. I can't get the 'upsize' wizard to work becuase it won't open the connection to SQL, even though I enter the userid and password of the SQL DB owner.  I get the following error: ===================================================== Connection failed: ============================================================= I have to say I'm stumped.  The rest of the Office 2010 suite works really well together - perhaps I'm missing something very simple? Thanks!     I guess I could export my tables as Excel 2003 and then import them using Integration Services, or install SQL Express on my laptop and 'upsize' to that instance, but SQL State: ‘0100’ SQL Server Error: 11004 [Microsoft][ODBC SQL Server Dirver][TCP/IP Sockets]ConnectionOpen (Connect()). Connection failed: SQL

Generating Multiple Excel Sheet using Reporting Services 2005.

  
<p> Hi, I'm currently developing a program using ASP.NET 2.0 (C#) and i am using Reporting Services 2005. One of the report requirements is to export a data to an excel with multiple excel sheet. Is there other way to generate multiple excel sheet using the reporting services? if yes, i would like to ask a sample code... Thanks in advance, JP  </p> 

Looping through Excel 2007 tables in SSIS?

  
Saw the (http://msdn.microsoft.com/en-us/library/ms345182(v=SQL.90).aspx) msdn article on MSDN, however I need to loop through Excel 2007 tables, which is not supported via the .Net Connection.  Is there a way to loop through the 2007 sheets without resorting to a script component?Steve Walker

How to access Macros in excel throw DotNet

  
Hi I want to use Macros in Excel throw DotNet. Give simple examples. Can you please any body, give some ideas.   thanks in advance. 

Reporting Services 2008 - Anonymous Access

  
Hello,   In SSRS 2005 I was able to grant anonymous access to my reports by changing the authentication mode for the Reporting Services virtual directory in IIS to Anonymous and then adding the 'account used for anonymous access' to the permissions on the appropriate Reporting Services directories (in report manager).   Now with SSRS 2008 there are no virtual directories in IIS.   I tried to follow the instructions given:   http://technet.microsoft.com/en-us/library/cc281310.aspx (Authentication Types in Reporting Services)   Which instructed me to use the custom AuthenticationType, and following the instructions here:   http://technet.microsoft.com/en-us/library/cc281383.aspx (How to: Configure Custom or Forms Authentication in Reporting Services)   which just got me an error: The Authentication Extension threw an unexpected exception or returned a value that is not valid: . (rsAuthenticationExtensionError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors   Any ideas on how to set up anonymous access to SSRS2008?   Thanks for any time and help!   -Tim

SharePoint and SQL reporting services. Report of access?

  
Hello,Can a report be created with SharePoint and SQL reporting services that shows who has accessed the document and when?Thanks in advanceSebastian  

OLAP and excel reporting

  
Hi guys:I have an OLAP database which I need to create a reporting facility for my users, Excel seems to be fine, I have a web site and need to place a link for users to donwload the reports.  The thing is that once the report is downloaded I cannot drill down or do any query operation because the report it is trying to connect to the remote server (of course once dowloaded it is outside of the network) It is there any sugestions on how to address this issue?, maybe storing the data in the excel sheet? what about using reporting services instead?Thanks in advance

Populate Excel Spreadsheet Starting At Specific Row

  
I am trying to populate an excel workbook using a data flow and it no longer works. I have a command button in cell a1, so the column headings need to start in row 3 and the data in row 4. also the column headings need to be red background. the data is ending up in the right row but no all the rows are also red. everything was working fine, now it is broken and cant figure out why. first i DROP TABLE, to clear the contents of the worksheet, this now only clears the column headings, the i CREATE TABLE, to put back the column headings, this works. Then using the data flow I populate the spreadsheet, it end up in row 3, which is right, but honetly i dnot know why. the problem is all the data rows are red. can anyone please help me with this. thanks.

Access from Reporting Service database in Sqlserver2005 to Sqlserver2008 R2 database

  
We are going to upgrade our databases from SQLServer 2005 to SQLServer 2008 R2. Today the databases including Reporting Services are running in SQLServer 2005 9.0.4211 and in a cluster environment. We are planning not to upgrade the Reporting Service database to Sqlserver 2008 R2. The Sqlserver2005 and Sqlserver2008 R2 will be run side-by-side.   My question is: Is it possible to access a database running in Sqlserver 2008 R2 from a Reporting service database that is running in Sqlserver 2005 (9.0.4211)?  

Access Control List tables in sql server 2005

  
Hi all, Can anybody help me in creating Acl tables.how to create them in sqlserver 2005 and how to use in my .net application

Prevent Excel access to SQL 2000

  
I see many questions on how to access SQL from Excel. I'm trying to do the opposite. We have a few purchased applications that use SQL as the database engine. With the capabilities in Excel 2007 making it so easy to connect to the SQL server, we see this as a security issue. We don't want our users to be able to access the database tables through any other methods besides through the application interface (a thick client in one instance and a web-based app in another). How can this be prevented? We are using AD for authentication and authorization and SQL 2000. Our DBA doesn't think this can be prevented. I'm the secuity guy and I'm responsible for data integrity. Are application roles a possibility? I understand they're difficult to maintian. From what I understand, this would allow the application to submit credentials to SQL without relying on AD credentials for individual users. Can SSL be used to encrypt the communications on both the thick and IIS applications? Thanks in Advance
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend