Excel Query Rights
Mar 21, 2008
Hi,
I am trying to allow a couple of users to log in via SQL server authentication through MS Excel to run database queries on selected views. I have created a database role for these users and given select permissions to the views I want them to see and that works fine. The only problem is that they also see all of the sys views that they should not be able to see. When I try to deny select permissions to these sys views I receive an error message indicating that I cannot deny select permissions to the sys view (there are several of them). Is there a way I can hide the sys views for the database role? I am using SQL 2005 and SQL Server Management Studio.
Thanks in advance.
View 1 Replies
ADVERTISEMENT
Jul 12, 2007
The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost
eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
View 8 Replies
View Related
Sep 19, 2005
I'm building a DTS package to query some tables and export the result to my local c: drive in Excel format.
I have a batch file that stamps the date and copies the file to another location (in the same package). It appears that the process is trying to stamp/copy the Excel file before the file is finished being written to. Is there any way to have the package wait for the completion of the file before the batch file starts? I have inserted a wait command (30 secs to 30 mins) within the batch file to no avail. I can run the package without the batch file and it completes within 10 seconds. Thanks.
View 1 Replies
View Related
Nov 11, 2007
Can someone please help?
My access database downloads the last 6 months stock market data i.e close, high, low price. However, I want to show the results in Excel but only the last three values for each stock (as I have over 1000 stocks).
For example:
qDate qTicker qOp qHi qLo qCl qVo
08/11/2006 AAT.L 98 100 98 99 2361001
09/11/2006 AAT.L 98 98 98 98 378001
10/11/2006 AAT.L 99 99 97 97 66001
08/11/2006 VOD.L 198 100 198 199 2361020
09/11/2006 VOD.L 198 198 198 198 378020
10/11/2006 VOD.L 199 199 197 197 66010
08/11/2006 ARM.L 298 200 298 299 1236100
09/11/2006 ARM.L 298 298 298 298 317800
10/11/2006 ARM.L 299 299 297 297 66100
but instead it displays the last 6 months data for every stock to the point where it is unable to display anymore records in Excel.
Does anyone have any SQL code to resolve this?
Thanks!
View 3 Replies
View Related
Feb 6, 2008
Need some help. We don't have any SSIS developers (and won't pay for any), so it has fallen to me to resolve.
I have been given a few tSQL queries were the results set needs to be in excel format and sent be either exchange our our secure FTP systems to vaious recipients. The SQL is very straight forward, but I don't know how to set up the various components inside VisStudio to pull this off.
Anyone know of an "idiot-proof" white paper or tech doc, preferably with screenshots, on how to do this step by step?
I've spent an hour on google and apparently am not using the correct "secret" word combination to find a link for something like this.
Thanks!
View 1 Replies
View Related
Mar 11, 2008
Hi all,
I've a question regarding manipulating a SQL query output so that I can copy the result set into Excel. The copy etc will be done via a .net application and is not where the problem is. The issue I am having is managing to return a result set in a tabular format which appears as I need it to in the excel.
The simplified down SQL result set looks like the following
Section Supervisor TargetVal
Section1 sup1 10
Section1 sup2 11
Section2 sup1 8
Section3 sup1 5
Section3 sup2 2
What i want to get out ouf this is something along the lines of the following:
sup1 sup2
Section1 10 11
Section2 8
Section3 5 2
Could somebody give me any suggestions on how to do this; i had thought the PIVOT operator may work but i'm not sure how to use it in this case.
Any help on this would be appreciated.
Thanks.
View 2 Replies
View Related
Apr 1, 2004
Using VBA, I have created a Sybase SQL query. I need to edit the query to read a date from a cell in an Excel worksheet.
Any tips on how to indicate that instead of a set date I can refer to a worksheet cell?
Any help is greatly appreciated.
Brandon
View 1 Replies
View Related
May 3, 2001
I want to give user right to create temp tables but I don't want to allow him modify any other tables, or any other rights. Any ideas?
View 1 Replies
View Related
Jul 8, 1999
Hello, I need to create a sp that allows a user(not sa) to reset passwords using sp_password. The part that I'm stuck on is how to login within the proc so that the user(not sa) can exec the sp_password as sa without having to give the user sa rights. I don't mind hard coding the sa password with the proc but I can not give sa password to the users. Do I need to somehow alter sp_password for this to work?
Any help is much appreciated.
Thanks.
View 1 Replies
View Related
Nov 22, 2000
Hi
Need to give a user permission to add logins and users to a database. Have tries to alias the user to DBO but it doesnt work. Is there a way to do it other than reassigning DBO permissions to the user.
Thanks
View 1 Replies
View Related
Jan 29, 2004
Is there a way in SQL server to grant "SA" rights to non-SA users for certain commands.
I know there's a way to do this in Sybase by creating a password protected role and then activating it within a stored procedure.
Thus, the specific right is only active for the brief duration of the stored procedure - which runs the particular command to be granted. The role is de-activated at the end of the stored procedure.
Any suggestions are greatly appreciated.
Thanks,
Isaac
View 2 Replies
View Related
Jul 20, 2005
I have a basic question regarding rights. What level of rights do Ihave to have to grant another user update rights? I don't want togive everyone owner rights. Can a person with update rights grantanother person update rights?Thanks.
View 1 Replies
View Related
May 10, 2007
I usually export the query using "Results To File" and save it as a csv. I think open MS Excel, open the csv file from Excel and then use the delimited wizard. Is there a quicker way with less steps?
View 3 Replies
View Related
Mar 2, 2003
I have a rather long SQL query to run with several date/time variables in it. The way I've done it in the past is to record a macro, paste the SQL into the query then afterwards turn the dates/times into variables by manually editing the code in the macro, so that next time it is run the SQL picks up the correct date/time and the correct data is retrieved. This has always worked okay in the past. However, the latest query I have is very long and complicated and I now appear to be unable to use the above method due, it appears, to the length of the query. It seems that the macro itself will only accept so many lines of an SQL query.
Please, does anybody know how to get round this problem?
Is it possible to save the query as an external query (.dqy) and also use variables within that query when a macro tries to run it? If so, how!? I've tried it, but it fails at the line ".Refresh BackgroundQuery:=False". With a general ODBC Error. I have put all of the necessary details in there, eg DSN etc, but no joy.
I've tried putting the SQL itself into several cells on a spreadsheet, altering the dates so that they a correct each time the query is run, concatenating the cells, turning the results into a variable and executing the SQL in a macro, but this also fails at the line ".Refresh BackgroundQuery:=False". Could this method be adapted?
Splitting the query into two shorter ones doesn't appear to be an option as there a loads of 'or' statements in it, several of which overlap. This would cause some results to be duplicated, which I would like to avoid!
Please, please help me!!
TIA
tobyjuggler
View 4 Replies
View Related
Jul 20, 2004
I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.
the command I am using is this.
declare @x varchar(300)
set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '
exec master..xp_cmdshell @x
go
View 7 Replies
View Related
Apr 6, 2006
select * from employees
I want to export result set into excel sheet by writing a query, can i do that? any one can write a query for me
View 2 Replies
View Related
Mar 1, 2007
Hi everyone,
I'm trying to query an excel file and I get a mistake. The query is as follows:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:ExcelFile.xls', 'select * from Sheet1')
and I get the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
I'm thanking any help that you can give.
Thanks,
Oscar.
View 7 Replies
View Related
Nov 17, 1998
Need to convert a sql query recordset to an ASCII tab deliminated to import into an excel spreadsheet.
Are there any work arounds for this.
Jah Red
jahred@globalmusic.com
View 1 Replies
View Related
Feb 20, 2015
I have been trying to find a way to export the results of a query to a csv for use in excel but cannot.
View 2 Replies
View Related
Feb 27, 2006
Hi,
I want to send the result of my query to an Excel file.How can I do it?
View 3 Replies
View Related
Jun 29, 2007
hi guys,
How could I export the result from Query? I'd like to run the query and at the same time the result must be saved in Excel Worksheet. is this possible? How?
Thank you very much.
View 5 Replies
View Related
Mar 8, 2006
Hi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
View 4 Replies
View Related
Dec 8, 2006
Hello,
I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;
-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_Accounts
I have the linked server configured as follows;
-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls;Persist Security Info=False
When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')
The following message appears, and no results are returned;
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?
Thank you for your help!
cdun2
View 1 Replies
View Related
Jun 25, 2006
I set up an Excel spreadsheet for several users that queries a SQL Server. I provided the spreadsheet to the users and set it to refresh when ever it is open. The query is working fine. The problem is the users are prompted with a SQL Server login screen every time they open the file. The login screen shows the server name, the username and the password. All they have to do is press 'OK' and the screen goes away. Why are they being prompted with this screen? What can I do to eliminate the users from even seeing this window?
I also receive this window whenever I open the file and I created it on the workstation where i'm opening it.
Thank you for any input or suggestions.
View 4 Replies
View Related
Oct 27, 2007
Hi all,
I am just wondering if anyone has a code to send query results to a spreadsheet package eg. MS Excel.
i) Automatically send the query result(s) to the package
ii) open the spreadsheet package;
Thanks.
View 1 Replies
View Related
Nov 23, 2006
Hello everyone,
i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.
Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?
thanks a lot.
View 4 Replies
View Related
Oct 2, 2007
Hello there,
how do i write a select query where i can pass parameter.for example
select * from employee where id = @empID
does not work when i use excel as my data source and type is OLE DB
View 1 Replies
View Related
Oct 30, 2001
Hi everybody,
The below I posted on SQL 2000 Forum about a week ago.
Any new thoughts................
I would like to get an input from as many people as possible on the following:
In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers.
DBA currently has a FULL access to every server.
In a few months we will be replacing the existing system with Windows 2000/SQL 2000.
LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that.
So I guess the main question(s) is:
What is the degree of involmment of DBA with Operating system?
Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do?
If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA?
What auditors saying about that?
Thanks a lot in advance,
Andrei
View 3 Replies
View Related
Aug 9, 2000
I have a user that is requesting sa rights on a test server. I prefer to give him aliased
dbo rights. What is the difference between the two?? What can he not do with dbo
that he could with sa??
View 1 Replies
View Related
Oct 19, 2000
hi, I am having a database in sql server 7.0. it has a web front end database. how can I grant access to the tables. do I create a guest logins in the security folder, then in the database user tab, I give access as read,write. Or there is another way to do it.
Thanks
Ali
View 2 Replies
View Related
Feb 20, 2001
Hi,
I have public and dbo rights on a sp.
I am trying to call this sp thru a EntityBean(Java).
But I am getting an error.
Can anyone tell me what all rights do I need to execute this stored proc.?
TIA.
Jay
View 1 Replies
View Related
Nov 28, 2005
Hi All,
I have a user that should only have the rights to view the jobs and database properties within Enterprise Manager. I am not sure how to do that. Can you please help? Thanks.
View 4 Replies
View Related
Nov 20, 2006
Hi All,
I've restored the dev db from the prod backup which overwrote the users and their rights in dev db. Is there any way that I can find out what those rights were? I have the list of users in dev db but not their rights . Thanks.
View 1 Replies
View Related
