Enumerate SQL Server Logins and Permissions

Haven't been able to find the answer to this after sever searches. So decided to open the thread myself.

I am trying to write a report enumerating logins in SQL Server and all database-level and object-level permissions granted to each login.

Can someone tell me what system objects I can query to fetch this information?

TIA.

Hi,

You can get the results by querying the following system views(cata log views):

Database-Level Views:

sys.database_permissions
sys.database_principals

Server-Level Views:

sys.server_permissions
sys.sql_logins
sys.server_principals

Thanks & Regards,

Kiran.Y

|||

That's just what I was looking for. Thanks much.

sql

Entities disappear from Report Builder Explorer when datasource is a cube

I have scoured the internet looking for someone who has run into this issue with no such luck!

Product: Reporting Services 2005
Analysis Services 2005

Our users use Report Builder as an ad-hoc report tool. Data sources for Report Builder come from Report Servers and Analysis Servers.

I have run into issues where a user will select a source for their report that is based on an AS cube. When they select an attribute (field) from a selected entity in the Report Builder Explorer and drop it on the canvas, all entities except the one the field was selected from disappear.

For example:

A cube has a fact table called "Annual Income" with dimension of employee and demographics. In Report Builder, the entities would be listed in the left hand Report Builder Explorer window as

Annual Income
Employee
Demographics

If I select attributes from Employee or Annual Income, as usual, you will see the related hierarchies of the remaining entities. However, if I select an attribute from Demographics, then the Annual Income and Employee entities just disappear and all I can see is the Demographics entity and its attributes.

Has anyone run into this and if so, any ideas? This is becoming extremely frustrating. I have deleted and rebuild cubes, individual dimensions, and everything else I can think of.

Your responses are appreciated,
Scott

Anyone?sql

Entering parameters resets other parameters

I am having a problem with reports where, after a parameter is changed, it
will remove the currently displayed report (replacing it with white) and on
some occassions reset some of the other parameters (even though there are no
dependencies). Is there some way to disable the refresh so that only
clicking "View Report" will change the report displayed?Has anyone else experienced this? We see the same thing and I would really
like to get rid of this behavior.
Thank you!
Jon
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:53AAA6CD-0B51-4879-A641-C580CD9819D6@.microsoft.com...
>I am having a problem with reports where, after a parameter is changed, it
> will remove the currently displayed report (replacing it with white) and
> on
> some occassions reset some of the other parameters (even though there are
> no
> dependencies). Is there some way to disable the refresh so that only
> clicking "View Report" will change the report displayed?

Entering Notes/Comments/Remarks to Report

Hi, there,

We intended to create a report that allows users to enter remarks, comments or additional notes in supporting the report.

Can it be done in SSRS? FYI, we are using SQL Server 2005 Enterprise.

Thank you.

Regards,

Yong Hwee

You want to annotate within the report, or you want to add extended properties to the report? If it is the first, then there is no way without changing the RDL or annotating the exported format. If it is the second, then you can use SetProperties/GetProperties to write/retrieve extended properties (including your annotations) programmatically.|||

Hi, John,

Thank you for your reply. Sorry for my ignorance. In fact, we are still thinking the way to cater this requirement. Currently, the report is in Excel workbook. Annotations are entered directly to the workbook. Users are producing this report every mth. If possible, we would like users to enter the annotations to the report and saved. It seems like the second method using SetProperties/GetProperties is the better choice but what are the possible ways of implemeting it?

We are looking for a intuitive solution that allows monthly reports to be saved and retrieved later. The results in the saved reports should not change irregardless of the underlying source. I think this is just like the 'report snapshot' in SSRS.

Thank you.

Regards,

Yong Hwee

|||You can use history snapshots for archiving previously generated reports, but we don't support setting/getting properties on individual snapshots, only the report item itself.|||

Hi, John,

Thank you for the reply.

Regards,

Yong Hwee

Entering Notes/Comments/Remarks to Report

Hi, there,

We intended to create a report that allows users to enter remarks, comments or additional notes in supporting the report.

Can it be done in SSRS? FYI, we are using SQL Server 2005 Enterprise.

Thank you.

Regards,

Yong Hwee

You want to annotate within the report, or you want to add extended properties to the report? If it is the first, then there is no way without changing the RDL or annotating the exported format. If it is the second, then you can use SetProperties/GetProperties to write/retrieve extended properties (including your annotations) programmatically.|||

Hi, John,

Thank you for your reply. Sorry for my ignorance. In fact, we are still thinking the way to cater this requirement. Currently, the report is in Excel workbook. Annotations are entered directly to the workbook. Users are producing this report every mth. If possible, we would like users to enter the annotations to the report and saved. It seems like the second method using SetProperties/GetProperties is the better choice but what are the possible ways of implemeting it?

We are looking for a intuitive solution that allows monthly reports to be saved and retrieved later. The results in the saved reports should not change irregardless of the underlying source. I think this is just like the 'report snapshot' in SSRS.

Thank you.

Regards,

Yong Hwee

|||You can use history snapshots for archiving previously generated reports, but we don't support setting/getting properties on individual snapshots, only the report item itself.|||

Hi, John,

Thank you for the reply.

Regards,

Yong Hwee