Opening Table in Management Studio is very slow

Dear Gurus

When I open a large table (say more than 1,000,000 Rows) in the SSMS by right clicking on the table name, it takes a very big time to fully open the table.More than 20 minutes for 1,000,000 records on a local instance.

SQL Server 2000 EM was extremely faster. Does any one knows a work around?

I need to be able to view and edit the data in SSMS.

Thanks in advance.

Parviz

I do not suggest ever opening a large table like this and editing the data through management studio. It is better to write queries to view the data as needed, and dml statements to update the data as needed.
Tim

Opening Solution clears previous queries in SSMS

Yikes!
Does anyone know of a way that I can open a solution but keep my current
query windows open? Every time I go to File -> Open -> Project/Solution, it
kills any query windows I was working in.
And while I'm here, is there a way to set SSMS to open with Solution the way
you can have it open a query window when you connect?
Help is appreciated. Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Catadmin wrote:
> Yikes!
> Does anyone know of a way that I can open a solution but keep my current
> query windows open? Every time I go to File -> Open -> Project/Solution,
it
> kills any query windows I was working in.
> And while I'm here, is there a way to set SSMS to open with Solution the w
ay
> you can have it open a query window when you connect?
> Help is appreciated. Thanks,
> Catadmin
Launch another instance of SSMS?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yeah, but I'd kinda like to open up the solution in my current instance of
SSMS.
I have a solution where I keep my commonly used code templates to fix data.
During the course of a day, I might be working on new development when
suddenly I get help desk tickets asking for data fixes because someone broke
something. Rather than lose what I've got open, or have to save & reopen it
,
or have to go to the whole Start->Programs hoo-ha (which is a pain when a
running query keeps pulls the focus back to SSMS while I'm trying to get to
something on the Start->Programs path), I'd like to just be able to open my
solution in Solution Explorer and have everything else remain the same.
Plus, if I open up another instance, I have to do cut-n-paste from one
instance to another. Bleargh. There's got to be a better way.
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Tracy McKibben" wrote:

> Catadmin wrote:
> Launch another instance of SSMS?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Another option is to just keep those scripts in a particular
directory. Then you can create a menu item in SSMS that will
open that particular directory. That's about what I've done
at some places for the same reasons - to get quick access to
fire fighting scripts and not lose whatever else I have
open. Just go to Tools, External Tools and create a new
entry - Command Explorer and Argument of the script
directory. That might work for you.
-Sue
On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:

>Yeah, but I'd kinda like to open up the solution in my current instance of
>SSMS.
>I have a solution where I keep my commonly used code templates to fix data.
> During the course of a day, I might be working on new development when
>suddenly I get help desk tickets asking for data fixes because someone brok
e
>something. Rather than lose what I've got open, or have to save & reopen i
t,
>or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>running query keeps pulls the focus back to SSMS while I'm trying to get to
>something on the Start->Programs path), I'd like to just be able to open my
>solution in Solution Explorer and have everything else remain the same.
>Plus, if I open up another instance, I have to do cut-n-paste from one
>instance to another. Bleargh. There's got to be a better way.|||Sue,
I see what you're talking about with the Tools -> External Tools, but I'm
not clear on what you mean by Command Explorer. Is there a particular .exe
that you're refering to with this?
Thanks,
Catadmin
"Sue Hoegemeier" wrote:

> Another option is to just keep those scripts in a particular
> directory. Then you can create a menu item in SSMS that will
> open that particular directory. That's about what I've done
> at some places for the same reasons - to get quick access to
> fire fighting scripts and not lose whatever else I have
> open. Just go to Tools, External Tools and create a new
> entry - Command Explorer and Argument of the script
> directory. That might work for you.
> -Sue
> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
>
>|||Sorry about that - it's not real clear from what I typed.
The exe is Explorer. For the Command, you reference:
%SystemRoot%\explorer.exe
so that it fires off Windows Explorer. It will start in
whatever directory you list for the Arguments.
-Sue
On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
[vbcol=seagreen]
>Sue,
>I see what you're talking about with the Tools -> External Tools, but I'm
>not clear on what you mean by Command Explorer. Is there a particular .exe
>that you're refering to with this?
>Thanks,
>Catadmin
>"Sue Hoegemeier" wrote:
>|||Thank you, Sue, but that's still not quite what I want. This doesn't enable
me to open an entire solution in SSMS while keeping current query windows
open.
If anyone from MS is reading this post, I'm sure there are other people who
would like to do the same thing I want to do (hint, hint).
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Sue Hoegemeier" wrote:

> Sorry about that - it's not real clear from what I typed.
> The exe is Explorer. For the Command, you reference:
> %SystemRoot%\explorer.exe
> so that it fires off Windows Explorer. It will start in
> whatever directory you list for the Arguments.
> -Sue
> On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
>
>|||No it won't open a solution. Like I said it will allow you
to open scripts in a specific directory but not a solution.
I think it's the only way around not losing what queries you
already have open if you don't want to open another SSMS
session. You could always search the product feedback site
to see if others posted this or post the request yourself:
http://lab.msdn.microsoft.com/productfeedback/
-Sue
On Tue, 19 Sep 2006 03:51:01 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:

>Thank you, Sue, but that's still not quite what I want. This doesn't enabl
e
>me to open an entire solution in SSMS while keeping current query windows
>open.
>If anyone from MS is reading this post, I'm sure there are other people who
>would like to do the same thing I want to do (hint, hint).
>Catadmin

Opening Solution clears previous queries in SSMS

Yikes!
Does anyone know of a way that I can open a solution but keep my current
query windows open? Every time I go to File -> Open -> Project/Solution, it
kills any query windows I was working in.
And while I'm here, is there a way to set SSMS to open with Solution the way
you can have it open a query window when you connect?
Help is appreciated. Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Catadmin wrote:
> Yikes!
> Does anyone know of a way that I can open a solution but keep my current
> query windows open? Every time I go to File -> Open -> Project/Solution, it
> kills any query windows I was working in.
> And while I'm here, is there a way to set SSMS to open with Solution the way
> you can have it open a query window when you connect?
> Help is appreciated. Thanks,
> Catadmin
Launch another instance of SSMS?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yeah, but I'd kinda like to open up the solution in my current instance of
SSMS.
I have a solution where I keep my commonly used code templates to fix data.
During the course of a day, I might be working on new development when
suddenly I get help desk tickets asking for data fixes because someone broke
something. Rather than lose what I've got open, or have to save & reopen it,
or have to go to the whole Start->Programs hoo-ha (which is a pain when a
running query keeps pulls the focus back to SSMS while I'm trying to get to
something on the Start->Programs path), I'd like to just be able to open my
solution in Solution Explorer and have everything else remain the same.
Plus, if I open up another instance, I have to do cut-n-paste from one
instance to another. Bleargh. There's got to be a better way.
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Tracy McKibben" wrote:
> Catadmin wrote:
> > Yikes!
> >
> > Does anyone know of a way that I can open a solution but keep my current
> > query windows open? Every time I go to File -> Open -> Project/Solution, it
> > kills any query windows I was working in.
> >
> > And while I'm here, is there a way to set SSMS to open with Solution the way
> > you can have it open a query window when you connect?
> >
> > Help is appreciated. Thanks,
> >
> > Catadmin
> Launch another instance of SSMS?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Another option is to just keep those scripts in a particular
directory. Then you can create a menu item in SSMS that will
open that particular directory. That's about what I've done
at some places for the same reasons - to get quick access to
fire fighting scripts and not lose whatever else I have
open. Just go to Tools, External Tools and create a new
entry - Command Explorer and Argument of the script
directory. That might work for you.
-Sue
On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Yeah, but I'd kinda like to open up the solution in my current instance of
>SSMS.
>I have a solution where I keep my commonly used code templates to fix data.
> During the course of a day, I might be working on new development when
>suddenly I get help desk tickets asking for data fixes because someone broke
>something. Rather than lose what I've got open, or have to save & reopen it,
>or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>running query keeps pulls the focus back to SSMS while I'm trying to get to
>something on the Start->Programs path), I'd like to just be able to open my
>solution in Solution Explorer and have everything else remain the same.
>Plus, if I open up another instance, I have to do cut-n-paste from one
>instance to another. Bleargh. There's got to be a better way.|||Sue,
I see what you're talking about with the Tools -> External Tools, but I'm
not clear on what you mean by Command Explorer. Is there a particular .exe
that you're refering to with this?
Thanks,
Catadmin
"Sue Hoegemeier" wrote:
> Another option is to just keep those scripts in a particular
> directory. Then you can create a menu item in SSMS that will
> open that particular directory. That's about what I've done
> at some places for the same reasons - to get quick access to
> fire fighting scripts and not lose whatever else I have
> open. Just go to Tools, External Tools and create a new
> entry - Command Explorer and Argument of the script
> directory. That might work for you.
> -Sue
> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >SSMS.
> >
> >I have a solution where I keep my commonly used code templates to fix data.
> > During the course of a day, I might be working on new development when
> >suddenly I get help desk tickets asking for data fixes because someone broke
> >something. Rather than lose what I've got open, or have to save & reopen it,
> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >something on the Start->Programs path), I'd like to just be able to open my
> >solution in Solution Explorer and have everything else remain the same.
> >
> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >instance to another. Bleargh. There's got to be a better way.
>|||Sorry about that - it's not real clear from what I typed.
The exe is Explorer. For the Command, you reference:
%SystemRoot%\explorer.exe
so that it fires off Windows Explorer. It will start in
whatever directory you list for the Arguments.
-Sue
On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Sue,
>I see what you're talking about with the Tools -> External Tools, but I'm
>not clear on what you mean by Command Explorer. Is there a particular .exe
>that you're refering to with this?
>Thanks,
>Catadmin
>"Sue Hoegemeier" wrote:
>> Another option is to just keep those scripts in a particular
>> directory. Then you can create a menu item in SSMS that will
>> open that particular directory. That's about what I've done
>> at some places for the same reasons - to get quick access to
>> fire fighting scripts and not lose whatever else I have
>> open. Just go to Tools, External Tools and create a new
>> entry - Command Explorer and Argument of the script
>> directory. That might work for you.
>> -Sue
>> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
>> <goldpetalgraphics@.yahoo.com> wrote:
>> >Yeah, but I'd kinda like to open up the solution in my current instance of
>> >SSMS.
>> >
>> >I have a solution where I keep my commonly used code templates to fix data.
>> > During the course of a day, I might be working on new development when
>> >suddenly I get help desk tickets asking for data fixes because someone broke
>> >something. Rather than lose what I've got open, or have to save & reopen it,
>> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>> >running query keeps pulls the focus back to SSMS while I'm trying to get to
>> >something on the Start->Programs path), I'd like to just be able to open my
>> >solution in Solution Explorer and have everything else remain the same.
>> >
>> >Plus, if I open up another instance, I have to do cut-n-paste from one
>> >instance to another. Bleargh. There's got to be a better way.
>>|||Thank you, Sue, but that's still not quite what I want. This doesn't enable
me to open an entire solution in SSMS while keeping current query windows
open.
If anyone from MS is reading this post, I'm sure there are other people who
would like to do the same thing I want to do (hint, hint).
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Sue Hoegemeier" wrote:
> Sorry about that - it's not real clear from what I typed.
> The exe is Explorer. For the Command, you reference:
> %SystemRoot%\explorer.exe
> so that it fires off Windows Explorer. It will start in
> whatever directory you list for the Arguments.
> -Sue
> On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Sue,
> >
> >I see what you're talking about with the Tools -> External Tools, but I'm
> >not clear on what you mean by Command Explorer. Is there a particular .exe
> >that you're refering to with this?
> >
> >Thanks,
> >
> >Catadmin
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Another option is to just keep those scripts in a particular
> >> directory. Then you can create a menu item in SSMS that will
> >> open that particular directory. That's about what I've done
> >> at some places for the same reasons - to get quick access to
> >> fire fighting scripts and not lose whatever else I have
> >> open. Just go to Tools, External Tools and create a new
> >> entry - Command Explorer and Argument of the script
> >> directory. That might work for you.
> >>
> >> -Sue
> >>
> >> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> >> <goldpetalgraphics@.yahoo.com> wrote:
> >>
> >> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >> >SSMS.
> >> >
> >> >I have a solution where I keep my commonly used code templates to fix data.
> >> > During the course of a day, I might be working on new development when
> >> >suddenly I get help desk tickets asking for data fixes because someone broke
> >> >something. Rather than lose what I've got open, or have to save & reopen it,
> >> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >> >something on the Start->Programs path), I'd like to just be able to open my
> >> >solution in Solution Explorer and have everything else remain the same.
> >> >
> >> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >> >instance to another. Bleargh. There's got to be a better way.
> >>
> >>
>|||No it won't open a solution. Like I said it will allow you
to open scripts in a specific directory but not a solution.
I think it's the only way around not losing what queries you
already have open if you don't want to open another SSMS
session. You could always search the product feedback site
to see if others posted this or post the request yourself:
http://lab.msdn.microsoft.com/productfeedback/
-Sue
On Tue, 19 Sep 2006 03:51:01 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Thank you, Sue, but that's still not quite what I want. This doesn't enable
>me to open an entire solution in SSMS while keeping current query windows
>open.
>If anyone from MS is reading this post, I'm sure there are other people who
>would like to do the same thing I want to do (hint, hint).
>Catadmin

Only seeing dbs that have access to

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...
>
|||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...
>

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.