opening reports with toggleitems set according to user defaults

i'm new to RS and have created reports with jump to url capability. the main
report has drill down capability up to 4 levels. i would like to know if it
is possible to open the report and have the toggleitems collapsed or expanded
(showing the next level if expanded) based on pre-defined choices. some
users might want drill-down expanded all the way to level 4 for some
groupings while only to level 2 on other groupings. for example, we create
financial reports that show monthly balances for staff salaries, fringe
costs, supplies, etc. a manager might want to see much detail on the staff
salaries and drill down another 2 levels to actually see staff names while
only want to see the highest level for fringe and not be concerned about how
much was paid for social security, retirement, etc..
rather than require the user each time to expand or collapse as they want
each time they run report, is there some way to save their choices to a table
and then through a query obtain their choices and set the toggleitem property
for each row based on its value.
i have found many examples of setting properties based on "value" property
but not other properties. i suspect that if it can be done, the "inscope"
function would be used. hope this makes sense!
any help you can give would be appreciated. thanksi think i found the answer to my question. RS doesn't appear to support
events and only exposes one reportitems property and that is "value". so i'm
guessing that what i'm asking is probably not possible within RS itself.
"dalem" wrote:
> i'm new to RS and have created reports with jump to url capability. the main
> report has drill down capability up to 4 levels. i would like to know if it
> is possible to open the report and have the toggleitems collapsed or expanded
> (showing the next level if expanded) based on pre-defined choices. some
> users might want drill-down expanded all the way to level 4 for some
> groupings while only to level 2 on other groupings. for example, we create
> financial reports that show monthly balances for staff salaries, fringe
> costs, supplies, etc. a manager might want to see much detail on the staff
> salaries and drill down another 2 levels to actually see staff names while
> only want to see the highest level for fringe and not be concerned about how
> much was paid for social security, retirement, etc..
> rather than require the user each time to expand or collapse as they want
> each time they run report, is there some way to save their choices to a table
> and then through a query obtain their choices and set the toggleitem property
> for each row based on its value.
> i have found many examples of setting properties based on "value" property
> but not other properties. i suspect that if it can be done, the "inscope"
> function would be used. hope this makes sense!
> any help you can give would be appreciated. thanks

Opening Multiple Reports In Adobe?

Does anyone know how to open two or more reports in adobe reader (pdf)? For
example, the user wants to print multiple reports in one whack, so I'd like
to be able to render several reports and ship them to adobe all at once. I
am using the render method of the web service to render reports along with
Response.OutputStream.Write to send the streaming content to the browser.
Any help or advice is greatly appreciated.
Thanks,
DBS5150Check
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=791554f6-b99b-425a-a23c-21eac16fa523&sloc=en-us.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBS5150" <DBS5150@.discussions.microsoft.com> wrote in message
news:FAADE7BE-16C3-45B6-8B1D-14919A5930E7@.microsoft.com...
> Does anyone know how to open two or more reports in adobe reader (pdf)?
For
> example, the user wants to print multiple reports in one whack, so I'd
like
> to be able to render several reports and ship them to adobe all at once.
I
> am using the render method of the web service to render reports along with
> Response.OutputStream.Write to send the streaming content to the browser.
> Any help or advice is greatly appreciated.
> Thanks,
> DBS5150

OpenDataSource Problem

begin
select @.datasource = 'Data Source="c:/'
+@.FOLDERNAME
+'/'
+@.FILENAME
+'";User ID=;Password=;Extended properties=Excel 5.0'
select
*
into
#excel_table
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @.DATASOURCE)...sheet1$
end

need to do this ... but using variables in opendatasource not allowed.

so how do we go about it

What i need to do is allow upload of any excel file ... may contain errors and then perform some checks on the data before putting it into another table with a fixed structure.select @.query ='create procedure sp_excel_upload as select
*
into
excel_table
from OpenDataSource( '+''''+'Microsoft.Jet.OLEDB.4.0'+''''+','+''''+'Da ta Source="'
+@.FOLDERNAME
+@.FILENAME
+'";User ID=;Password=;Extended properties=Excel 5.0'+'''' + ')...sheet1$'
exec (@.query)
exec sp_excel_upload
/* put checks here */
select * into final_table from excel_table
drop procedure sp_excel_upload
drop table excel_table

How much performance overhead would this approach add on say about 40 files daily ?

Any ideas ??|||Guys .. Gurus ...

Nobody has any thoughts about this ??sql

open xml output in word

I has a sproc that creates xml output using 'Select ... for xml', but I want
my code to automatically open it in Word, so the user will see the output in
word and can save it whereever they want. I tried the xp_cmdShell (after
enabling it) - either I'm not using it correctly or something.
This is what I'm doing:
declare @.RV as integer
exec @.rv = uspCatalogtoXML
xp_cmdshell 'word.exe ' + @.RV
but I know the xp_cmdShell line is wrong.
Anyone help?
Reference: http://support.microsoft.com/kb/210565
2000,2003
Reference: http://office.microsoft.com/en-us/word/HP101640101033.aspx
2007
Depending on what version of word you will need to navigate to the proper
folder and the use the winword.exe...
example :xp_cmdshell 'C:\Program Files\Microsoft Office\Office\Winword.exe
/a /w'
Problem is, I do not believe you can pass in an XML file for 2000 or 2003.
In 2007 you can use the /pxslt sitch but you will have to save the xml and
the xslt as a file first.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:DC26E85E-CFFC-4A63-9989-C361B84634E0@.microsoft.com...
>I has a sproc that creates xml output using 'Select ... for xml', but I
>want
> my code to automatically open it in Word, so the user will see the output
> in
> word and can save it whereever they want. I tried the xp_cmdShell (after
> enabling it) - either I'm not using it correctly or something.
> This is what I'm doing:
> declare @.RV as integer
> exec @.rv = uspCatalogtoXML
> xp_cmdshell 'word.exe ' + @.RV
> but I know the xp_cmdShell line is wrong.
> Anyone help?
>

open xml output in word

I has a sproc that creates xml output using 'Select ... for xml', but I want
my code to automatically open it in Word, so the user will see the output in
word and can save it whereever they want. I tried the xp_cmdShell (after
enabling it) - either I'm not using it correctly or something.
This is what I'm doing:
declare @.RV as integer
exec @.rv = uspCatalogtoXML
xp_cmdshell 'word.exe ' + @.RV
but I know the xp_cmdShell line is wrong.
Anyone help?Reference: http://support.microsoft.com/kb/210565
2000,2003
Reference: http://office.microsoft.com/en-us/w...1640101033.aspx
2007
Depending on what version of word you will need to navigate to the proper
folder and the use the winword.exe...
example :xp_cmdshell 'C:\Program Files\Microsoft Office\Office\Winword.exe
/a /w'
Problem is, I do not believe you can pass in an XML file for 2000 or 2003.
In 2007 you can use the /pxslt sitch but you will have to save the xml and
the xslt as a file first.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:DC26E85E-CFFC-4A63-9989-C361B84634E0@.microsoft.com...
>I has a sproc that creates xml output using 'Select ... for xml', but I
>want
> my code to automatically open it in Word, so the user will see the output
> in
> word and can save it whereever they want. I tried the xp_cmdShell (after
> enabling it) - either I'm not using it correctly or something.
> This is what I'm doing:
> declare @.RV as integer
> exec @.rv = uspCatalogtoXML
> xp_cmdshell 'word.exe ' + @.RV
> but I know the xp_cmdShell line is wrong.
> Anyone help?
>

Open Report Builder and Link to Report Directly?

Hi,

I've created a simple webpart that lists all the reports that a user has created using the Report Builder and next to each line item is an "Edit" link. What I would like to do is open up the Report Builder application and pass a parameter of the report path or something like that so that report builder opens up the report automatically.

Is this possible?

Thanks

Tristian

http://blogs.msdn.com/bobmeyers/archive/2006/01/24/517083.aspx|||THanks, this worked great!

Open a new report in a new window from existing

Hello all:
I need to open a new RS report from an existing RS report when the user
selects a distinct week # field.
I realize I right click the field / Properties / Advanced / Navigation I
believe I need to use the jump to URL.
I suspect I need a java script to create the new window with the report.
Can someone provide for me an example of the Java script necessary and
clarify my directions.
Also, does anyone know if this is explained WHERE I can find it in the
Hitchhikers Guide to Reporting Services - I have a copy and can not find the
resolution. I understand it is in Teo's book MS RS in Action; which I dont
own. All suggestions are welcome.
Thanks you in advance
Stuart
inventoryguy@.hotmail.comThis was introduced with SP1 which might be why it is not in your book. Here
is the syntax:
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you remove the format and encoding it will come up in HTML (the default
format if nothing is specified)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart@.ICS" <inventory@.inventory.com> wrote in message
news:%23mau5rd9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> Hello all:
> I need to open a new RS report from an existing RS report when the user
> selects a distinct week # field.
> I realize I right click the field / Properties / Advanced / Navigation I
> believe I need to use the jump to URL.
> I suspect I need a java script to create the new window with the report.
> Can someone provide for me an example of the Java script necessary and
> clarify my directions.
> Also, does anyone know if this is explained WHERE I can find it in the
> Hitchhikers Guide to Reporting Services - I have a copy and can not find
> the resolution. I understand it is in Teo's book MS RS in Action; which I
> dont own. All suggestions are welcome.
> Thanks you in advance
> Stuart
> inventoryguy@.hotmail.com
>|||Hello Bruce:
I get a URI Scheme is not valid error when I test the report.
My goal to to have a link off a field in report 1 when clicked open another
RS report in a new window.
The line I used is:
"javascript:void(window.open("https://server/ReportServer?%2fPRJfolder%2fRPTname"
& "&rs:Command=Render", "_blank",
"location=no,toolbar=no,left=100,top=100,height=600,width=800"))"
We have SP1 loaded.
Can you tell me where we have errored in our code?
If we need to pass parameters how would the line be different, I the
similances of that in your code but would like to make certain of all your
suggestions.
Again, thank you for the help.
Stuart
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eKs5Vbf9FHA.1844@.TK2MSFTNGP11.phx.gbl...
> This was introduced with SP1 which might be why it is not in your book.
> Here is the syntax:
> Here is an example of a Jump to URL link I use. This causes Excel to come
> up with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you remove the format and encoding it will come up in HTML (the default
> format if nothing is specified)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Stuart@.ICS" <inventory@.inventory.com> wrote in message
> news:%23mau5rd9FHA.2792@.TK2MSFTNGP11.phx.gbl...
>> Hello all:
>> I need to open a new RS report from an existing RS report when the user
>> selects a distinct week # field.
>> I realize I right click the field / Properties / Advanced / Navigation I
>> believe I need to use the jump to URL.
>> I suspect I need a java script to create the new window with the report.
>> Can someone provide for me an example of the Java script necessary and
>> clarify my directions.
>> Also, does anyone know if this is explained WHERE I can find it in the
>> Hitchhikers Guide to Reporting Services - I have a copy and can not find
>> the resolution. I understand it is in Teo's book MS RS in Action; which
>> I dont own. All suggestions are welcome.
>> Thanks you in advance
>> Stuart
>> inventoryguy@.hotmail.com
>>
>|||One thing I suggest, try it without any report first. Open up another web
page, google or whatever. Then you can make sure that you are using it
properly. Then open a report. One thing I am not sure of is the syntax you
you for location etc).
Looking at you have done, you are not doing what I did. I have single quotes
in places where you modified it to use double quotes. You are assembling a
string here. I use double quotes only between strings and then I have and &
concatenating the string. Look at mine closely, it was a working example.
The only place double quotes are used will have an ampersand either before
it or after it depending on whether I am starting a new string or ending it.
Looking at yours (not even worrying about what you have actual put there)
but without a doubt it is totally wrong just because of your use of double
quotes.
One easy thing to do, have a text box in a report. No dataset. Just a report
with a single text box. Set the source of the textbox to this expression so
you can see the result. Until you get the quotes correct it will keep
erroring out. Then once you have that correct you can see what your
resulting string looks like.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart@.ICS" <inventory@.inventory.com> wrote in message
news:%23TDOQ0f9FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Hello Bruce:
> I get a URI Scheme is not valid error when I test the report.
> My goal to to have a link off a field in report 1 when clicked open
> another RS report in a new window.
> The line I used is:
> "javascript:void(window.open("https://server/ReportServer?%2fPRJfolder%2fRPTname"
> & "&rs:Command=Render", "_blank",
> "location=no,toolbar=no,left=100,top=100,height=600,width=800"))"
> We have SP1 loaded.
> Can you tell me where we have errored in our code?
> If we need to pass parameters how would the line be different, I the
> similances of that in your code but would like to make certain of all your
> suggestions.
> Again, thank you for the help.
> Stuart
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eKs5Vbf9FHA.1844@.TK2MSFTNGP11.phx.gbl...
>> This was introduced with SP1 which might be why it is not in your book.
>> Here is the syntax:
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you remove the format and encoding it will come up in HTML (the
>> default format if nothing is specified)
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Stuart@.ICS" <inventory@.inventory.com> wrote in message
>> news:%23mau5rd9FHA.2792@.TK2MSFTNGP11.phx.gbl...
>> Hello all:
>> I need to open a new RS report from an existing RS report when the user
>> selects a distinct week # field.
>> I realize I right click the field / Properties / Advanced / Navigation I
>> believe I need to use the jump to URL.
>> I suspect I need a java script to create the new window with the report.
>> Can someone provide for me an example of the Java script necessary and
>> clarify my directions.
>> Also, does anyone know if this is explained WHERE I can find it in the
>> Hitchhikers Guide to Reporting Services - I have a copy and can not find
>> the resolution. I understand it is in Teo's book MS RS in Action; which
>> I dont own. All suggestions are welcome.
>> Thanks you in advance
>> Stuart
>> inventoryguy@.hotmail.com
>>
>>
>

OOpening report in new page without using url.

Sounds simple but here is what I need. I have an aspx webform that asks the user for 19 parameters that my report requires. I need to validate the form and have a button that the user can click and have the report open in a new window. Like I said the report has 19 parameters and we would like to avoid passing the parameters in the url. Here is what I’ve tried:

Using cross-page postback I can pass the parameters captured in the Getparameters webform to the viewreport webpage but it opens on the same window. I can use a regular button or a linkbutton and added “onclick” attributes to run clientside validation before submitting a form. The viewreport webpage has a reportviewer control. I just need to open the report in a new window.

Tried javascript window.open in several places (btnView “onclick”, linkbutton “onclick”, registerclientscriptblock (runs after the form is posted) all giving me the error “object null reference exception” on the report parameters.

Lastly I’m trying to use SOAP and running render method for button on_click but I keep getting the error report path not valid report path needs to be less than 260 characters and must start with slash. Here is the code that I’m using for render method:

Dim rsAsNew RSWebReference.ReportingService

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Render Arguments

Dim resultAsByte =Nothing

Dim reportPathAsString ="/Sales/MonthlySales"

Dim formatAsString ="PDF"

Dim historyIDAsString =Nothing

Dim devInfoAsString = “<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"

'Prepare report Parameters

Dim parameters(0)As RSWebReference.ParameterValue

parameters(0) =New RSWebReference.ParameterValue

parameters(0).Name ="Date"

parameters(0).Value ="09/10/2006"

Dim credentials()As RSWebReference.DataSourceCredentials =Nothing

Dim showhidetoggleAsString =Nothing

Dim encodingAsString =""

Dim mimeTypeAsString =""

Dim warnings()As RSWebReference.Warning =Nothing

Dim reportHistoryParametersAs RSWebReference.ParameterValue() =Nothing

Dim streamIDAsString() =Nothing

rs.Render("MonthlySales","PDF", historyID, devInfo, parameters, credentials, showhidetoggle, encoding, mimeType, reportHistoryParameters, warnings, streamID)

I just want to know if what I want to accomplish is even posible because I’ve been at this for over a week, going Crazy!

I thought maybe the report needed credentials set instead of nothing, but don’t know how to do that. Using VS 2005, SQL 2005

Any help is immensely appreciated!

I've tried a bunch of different things myself and can't seem to get it. It would be nice that after you call rs.Render(), rs would have a property set to the url of what you just ran. There is a method rs.GetReportLink(ReportName as String) but it just returns the url to the report with none of the query string. My guess is you'll be writing a URL Generator and using a RegisterStartUp script to call a javascript:window.open(). Works well for me.|||

I finally figured it out. Seems that I had the incorrect webreference and was sending the wrong parameters, but here is my solution.

I added a web reference to ReportExecution2005 and used the following code:

Response.BufferOutput = True

Response.ClearContent()

Response.ClearHeaders()

Response.AddHeader("content-type", ContentType)

Response.AddHeader("Content-Length", result.Length.ToString)

Response.AddHeader("content-disposition", "attachement;filename='" & FileName & "'")

Response.BinaryWrite(result)

Response.Flush()

Response.Clear()

'End the response

Response.End()

By using content-disposition "attachement" I have the browser ask the user to open or save the report. To have this open in a new window I had to go with the form target=_blank. I did find that different MIME types yield different results. For example using "application/pdf" will open the pdf document in a the recently opened new browser window. Using "application/x-pdf" will open the pdf document in acrobat session leaving a blank page behind. I also tried exporting the report to excel, but the onlyl issue with excel is that it renders the report formated so some columns do not match the cells. I haven't figured out how to render that correctly.

Only update changed columns

I have a stored procedure that inserts values into five columns of a
table. I need another stored procedure that will allow the user to pass
one or more of those parameters and update only the column for the data
that was passed. In other words, the update may only have one or two of
the parameters that was originally provided in the insert. Therefore, I
do not want to update the columns that did not change. What is the
proper way to handle this situation?binder (rgondzur@.gmail.com) writes:

Quote:

Originally Posted by

I have a stored procedure that inserts values into five columns of a
table. I need another stored procedure that will allow the user to pass
one or more of those parameters and update only the column for the data
that was passed. In other words, the update may only have one or two of
the parameters that was originally provided in the insert. Therefore, I
do not want to update the columns that did not change. What is the
proper way to handle this situation?

In our application, most insert/update procedures exposes about all
columns in the table, and when the client fills the GUI, it reads all
columns from the table. Thus there is little reason to check what
actually changed. (Except for auditing.)

If you want to expose an interface of a procedure where the caller only
specifies what the to change, because it has for some reason not read
all existing columns (and this makes sense for a client that is a
monitori or similar), you could do:

CREATE PROCEDURE update_sp @.keyvalue sometype,
@.par1 someothertype = NULL,
@.par2 yetanothertype = NULL,
... AS

UPDATE tbl
SET col1 = coalesce(@.par1, col1),
col2 = coalesce(@.par2, col2),
...
WHERE keycol = @.keyvalue

That is, the procedure accepts parameters for all updatable columns,
but the caller passes non-NULL values for those it does not want to
change. This presumes that a caller never want to set a column to
NULL. If this is a required, you need to add one flag parameter for
each value parameter.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Only seeing dbs that have access to

Hi,
I came across a script to update the sp_MSdbuseraccess SP so that when a
user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
only see databases that they have access to
(http://support.microsoft.com/default.aspx/kb/889696). This works fine apart
from if users are using the new SQL Management Studio tool to connect to the
SQL 2000 server. Now they can see all databases again! Has anyone overcome
this issue?
Thanks - Kate.This is most probably not possible. SSMS doesn't use stored procedures when doing things like
querying for what databases exist (*). And there isn't anything in the 2000 engine for this. There
is in 2005, where you by default (at the engine level) only see objects that you actually have
access to (except for databases - you see all, but this is controllable though a REVOKE statement).
(*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
folder or the drop-down lost for a query windows. In both cases there was a SELECT against a system
table. This was against a 2005 instance (I don't have a setup right now where I can connect SSMS
against a 2000 instance), but I very much doubt it is any different.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access to
> (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users are
> using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see all
> databases again! Has anyone overcome this issue?
> Thanks - Kate.|||Ok great - what was the SQL statement?
thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||you mention a REVOKE statement for SQL 2005 installs - could you elaborate
on this?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||Basically a SELECT from sys.databases. Remember, I was running against a 2005 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELECT from sysdatabases).
But you can easily check this yourself using the same method I did (using Profiler).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eLAEWnf3HHA.464@.TK2MSFTNGP02.phx.gbl...
> Ok great - what was the SQL statement?
> thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures when doing things like
>> querying for what databases exist (*). And there isn't anything in the 2000 engine for this.
>> There is in 2005, where you by default (at the engine level) only see objects that you actually
>> have access to (except for databases - you see all, but this is controllable though a REVOKE
>> statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
>> folder or the drop-down lost for a query windows. In both cases there was a SELECT against a
>> system table. This was against a 2005 instance (I don't have a setup right now where I can
>> connect SSMS against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
>> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access
>> to (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users
>> are using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see
>> all databases again! Has anyone overcome this issue?
>> Thanks - Kate.
>|||Check out the "VIEW ANY DATABASE" privilege. Try below, for example:
CREATE LOGIN x WITH PASSWORD = 'hjus8#."3'
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --14 rows
REVERT
DENY VIEW ANY DATABASE TO x
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --2 rows
REVERT
GO
DROP LOGIN x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:Oj0C6nf3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> you mention a REVOKE statement for SQL 2005 installs - could you elaborate
> on this?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures
>> when doing things like querying for what databases exist (*). And there
>> isn't anything in the 2000 engine for this. There is in 2005, where you by
>> default (at the engine level) only see objects that you actually have
>> access to (except for databases - you see all, but this is controllable
>> though a REVOKE statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when
>> expanding the databases folder or the drop-down lost for a query windows.
>> In both cases there was a SELECT against a system table. This was against
>> a 2005 instance (I don't have a setup right now where I can connect SSMS
>> against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message
>> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>