Showing posts with label users. Show all posts
Showing posts with label users. Show all posts

Friday, March 30, 2012

Hide Parameters from User.

Hi,
Is there any way that the parameters passed from the URL is not shown to the users. Making the "Prompt User" unchecked and passing the parameter through the URL gave some error. Is there a work around.
Thanks.
--
Message posted via http://www.sqlmonster.comTry appending this to your URL:
&rc:Parameters=false
"Prashanth Rao via SQLMonster.com" wrote:
> Hi,
> Is there any way that the parameters passed from the URL is not shown to the users. Making the "Prompt User" unchecked and passing the parameter through the URL gave some error. Is there a work around.
> Thanks.
> --
> Message posted via http://www.sqlmonster.com
>

Hide parameter for some users

I'm trying to decide whether I can use sql reports for my application, and
the main problem I've run into thus far is that my available parameters need
to change based on the user who is running the report. For instance,
low-level users should only be able to see data about themselves, whereas
managers should be able to select from a drop-down list of all users. Is
there any way to set the user prompt/visibility for a parameter based on the
role of the person running the report?The easiest way I can think of to manage this is to have two versions of the
same report. With the regular version, pass =User!UserID into the query
parameter. In the manager's version, populate the same parameter from a
drop-down list. Then set different permissions when you deploy the reports.
If you need to do it in a single report, you might be able to write some
custom code to pass in flags to the query and work out the difference
between managers and employees in your data source query.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"jgephart" <jgephart@.discussions.microsoft.com> wrote in message
news:36979870-6D83-4C49-B1E6-88D8CBC51678@.microsoft.com...
> I'm trying to decide whether I can use sql reports for my application, and
> the main problem I've run into thus far is that my available parameters
> need
> to change based on the user who is running the report. For instance,
> low-level users should only be able to see data about themselves, whereas
> managers should be able to select from a drop-down list of all users. Is
> there any way to set the user prompt/visibility for a parameter based on
> the
> role of the person running the report?|||Thanks, Jeff. This will affect a whole series of reports, so I don't really
want to maintain separate versions. I think I'm going to be able to go with
passing in User!UserID to the query that builds the drop-down list of users.
I can't hide the drop-down list, but at least I can make sure it's only
populated with a single user if that user doesn't have permission to see
other people's data.
"Jeff A. Stucker" wrote:
> The easiest way I can think of to manage this is to have two versions of the
> same report. With the regular version, pass =User!UserID into the query
> parameter. In the manager's version, populate the same parameter from a
> drop-down list. Then set different permissions when you deploy the reports.
> If you need to do it in a single report, you might be able to write some
> custom code to pass in flags to the query and work out the difference
> between managers and employees in your data source query.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---|||To protect it on the back side (from someone simply altering the URL to look
at someone else's data) you could always create a permissions table that
contains all of the usernames that each user is allowed to look at, and have
that table always be in the source query join, with User!UserID passed into
the query. We're doing a variation on this (a join to a parameterized UDF)
for our data security solution.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"jgephart" <jgephart@.discussions.microsoft.com> wrote in message
news:F5C7BDD7-4F0D-488C-80CB-7F81201B08A4@.microsoft.com...
> Thanks, Jeff. This will affect a whole series of reports, so I don't
> really
> want to maintain separate versions. I think I'm going to be able to go
> with
> passing in User!UserID to the query that builds the drop-down list of
> users.
> I can't hide the drop-down list, but at least I can make sure it's only
> populated with a single user if that user doesn't have permission to see
> other people's data.
>
> "Jeff A. Stucker" wrote:
>> The easiest way I can think of to manage this is to have two versions of
>> the
>> same report. With the regular version, pass =User!UserID into the query
>> parameter. In the manager's version, populate the same parameter from a
>> drop-down list. Then set different permissions when you deploy the
>> reports.
>> If you need to do it in a single report, you might be able to write some
>> custom code to pass in flags to the query and work out the difference
>> between managers and employees in your data source query.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---|||You could use linked reports instead, possibly in conjunction with the
My Reports feature. Linked reports are effectively shortcuts to a
report.
But, the nice touch is that you can set the parameters to behave a bit
differently, for instance there is a prompt user check box on each
parameter in the linked reports properties. This doesn't affect the
original report. If you update the original, the linked report will
reflect these changes too. Security can be set completely independantly
too.
Chris
Jeff A. Stucker wrote:
> To protect it on the back side (from someone simply altering the URL
> to look at someone else's data) you could always create a permissions
> table that contains all of the usernames that each user is allowed to
> look at, and have that table always be in the source query join, with
> User!UserID passed into the query. We're doing a variation on this
> (a join to a parameterized UDF) for our data security solution.sql

Hide Parameter field, rc:parameters ?

Hi,
I want to reference from one reports to another and I don't want my users to
see the parameters used. In case of a url-reference I can use the
rc:parameters = false to disable the parameter field in the viewer.
How can I reach the very same behaviour when I reference from one report to
another (navigation) ?
Thanks in advance,
CarstenHow r u using the reference to another report? is it through a web link?
or a child report?
"Carsten Schlotmann" <cschlotmann@.dornbracht.de> wrote in message
news:e5XZHl2rEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to reference from one reports to another and I don't want my users
> to
> see the parameters used. In case of a url-reference I can use the
> rc:parameters = false to disable the parameter field in the viewer.
> How can I reach the very same behaviour when I reference from one report
> to
> another (navigation) ?
> Thanks in advance,
> Carsten
>|||Hi,
it is through a child report.
Regards,
Carsten
"avnrao" <avn@.newsgroups.com> schrieb im Newsbeitrag
news:uaKHoo5rEHA.3588@.tk2msftngp13.phx.gbl...
> How r u using the reference to another report? is it through a web link?
> or a child report?
> "Carsten Schlotmann" <cschlotmann@.dornbracht.de> wrote in message
> news:e5XZHl2rEHA.516@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I want to reference from one reports to another and I don't want my
users
> > to
> > see the parameters used. In case of a url-reference I can use the
> > rc:parameters = false to disable the parameter field in the viewer.
> > How can I reach the very same behaviour when I reference from one report
> > to
> > another (navigation) ?
> >
> > Thanks in advance,
> > Carsten
> >
> >
>

Hide 'New Subscription' in the explorer bar

Hello

We do not want the end users to know of the subscription capability. How do we hide 'New Subscription' link for a report.

Thanks

InderSunny,

You can edit your Browser role and remove the capabilties of the user - Report Directory/Properties/Security should get you going.

Ham

|||

In the security tab only following roles are available

Browser

May view folders, reports and subscribe to reports.

Content Manager

May manage content in the Report Server. This includes folders, reports and resources.

My Reports

May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.

Publisher

May publish reports and linked reports to the Report Server.

Report Builder

May view report definitions.

Which one should be selected so that the user is only able to view folders and reports and not subscription.

|||

indersunny,

Click on the Browser and you will get : - Assign the approciate security.

elect one or more tasks to assign to the role.

Task

Description

Consume reports

Reads report definitions

Create linked reports

Create linked reports and publish them to a report server folder.

Manage all subscriptions

View, modify, and delete any subscription regardless of who owns the subscription.

Manage data sources

Create and delete shared data source items; and modify data source properties.

Manage folders

Create, view and delete folders; and view and modify folder properties.

Manage individual subscriptions

Each user can create, view, modify and delete subscriptions that he or she owns.

Manage models

Create, view, and delete models; and view and modify model properties.

Manage report history

Create, view, and delete report history snapshots; and modify report history properties.

Manage reports

Create, view, and delete reports; and modify report properties.

Manage resources

Create, modify and delete resources, and view and modify resource properties.

Set security for individual items

View and modify security settings for reports, folders, resources, and shared data sources.

View data sources

View shared data source items in the folder hierarchy; and view data source properties.

View folders

View folder items in the folder hierarchy; and view folder properties.

View models

View models in the folder hierarchy, use models as data sources for a report, and run queries against the model to retrieve data.

View reports

View reports and linked reports in the folder hierarchy; view report history snapshots and report properties.

View resources

View resources in the folder hierarchy; and view resource properties.

Ham

|||

InderSunny,

Can you mark my post as answer so our solution will be found by others who are looking for the same solution?

Thanks,

Ham

Hide logins:-)

Hi knights,
Pls show me the way to prevent the other users seeing all logins when they logon into SQL Server. Thnks in advance!You can set permissions on master..sylogins view and master..sysxlogins.
There may be a procedure just like if we wanna restrict a user to change password using sp_password.
Tamperring with system tables is not a good idea though. Folks may have better options, wait for them.

Howdy.|||See if this (http://www.sqlservercentral.com/columnists/rmarda/letsblockthedba_printversion.asp) can give you some ideas. But why do you need to "hide" logins? Are passwords too easy to guess? Maybe that's what you need to address, hey?!

hide field from user

I have an app that connects to sqlepress. When run the field are loaded from the db. There are some fields that I don't want some of the users to see. Is there a way to do this without hardcoding the 'username' in the applicaton ?

I looked at the explicit permissions for username for the particular table, but I don't understand some of the terms listed.

Alter

Control

References

View definition

Normally you do this by creating a View that exposes just the columns that you want to show to the users. I don't know of any way to control column level access by user, particularly if you don't tell SQL who the users is. I would say that you create specific groups to control access level, then build Views with the right set of columns for each group. Then you can call the appropriate View based on the group membership.

Mike

sql

Hide Databases

Hi Folks,
I=B4m sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query=20
Analyzer at client side. It=B4s working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases=20
to non-owners ?
Thanks
Alex Decarli.This is not possible in SQL2000 Enterprise Manager. Metadata security is
pretty lax.You can choose to hide System databases if you wish , by going
into Edit SQL Server Registration Properties and uncheck Show system
databases and system objects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alex Decarli" <anonymous@.discussions.microsoft.com> wrote in message
news:079401c3dc1a$9849ce20$a501280a@.phx.gbl...
Hi Folks,
Im sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query
Analyzer at client side. Its working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases
to non-owners ?
Thanks
Alex Decarli.

Wednesday, March 28, 2012

Hide Databases

Hi Folks,
I=B4m sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query=20
Analyzer at client side. It=B4s working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases=20
to non-owners ?
Thanks
Alex Decarli.This is not possible in SQL2000 Enterprise Manager. Metadata security is
pretty lax.You can choose to hide System databases if you wish , by going
into Edit SQL Server Registration Properties and uncheck Show system
databases and system objects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alex Decarli" <anonymous@.discussions.microsoft.com> wrote in message
news:079401c3dc1a$9849ce20$a501280a@.phx.gbl...
Hi Folks,
Im sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query
Analyzer at client side. Its working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases
to non-owners ?
Thanks
Alex Decarli.

Hide database structure from users and competitors

Hi,

Is there any way to hide the database structure in SQL Server 2000, diagrams etc from my customers and potential competitors ?

Regards

No.

Thanks
Laurentiu

|||

Thank you but it′s a big problem no ?

If I document my software all people can read my structure.

|||

Yes, this problem was discussed on this forum before. See, for example: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1.

Thanks
Laurentiu

sql

Hide database names from unauthroized users in SSMS

If a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user

TIA,

Barkingdog

Hi,

you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I found the server-level permission "View any database", denied it to the test user, and found exactly that. The user couldn't see (under SSMS) any databases whatsoever including the one he is the DBO of! How can I assign him permissions to see that one database but no others in the SSMS tree?

TIA,

Barkingdog

P.S. The evidence is if I removed that permission from "public" then no one -- maybe even the sa? - could see any databases in SSMS.!

|||

How are you making the user an owner? Are you making him a member of db_owner or are you using the alter authorization statement to make the user the actual owner of the database?

You can tell by looking at the owner_sid in sys.databases - the sid should point to the owning login. If the login is the owner of the database, denying that user view any database will not prevent him from viewing the database in sys.databases or Management Studio.

Jack Richins
SDE Sql Server

|||

I made him a member of the db_owner.

When I run

sp_helpuser <DBName>

it returns x01 as the SID for the username dbo.

When I run

use DBName

select * from sys.databases

it returns.......

0x010500000000000515000000AAB950D6A501....

for the owner sid of the database.

So the user is not the DBO (though he belongs to that role).

Then back to my original question. How can I deny a user from seeing any databses in the SSMS tree that he does not have permission to access?

TIA,

Barkingdog

|||

DENY VIEW ANY DATABASE to the test login. This prevents the user from seeing any databases for which the user is not the DBO. You can make the user a DBO by using ALTER AUTHORIZATION DATABASE::<database> TO <login>. Being a member of db_owner is not sufficient to see the database if "view any database" was denied.

This is not very clean and may not work for you if you can't make this user login the actual DBO of the database you want him to administer or have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.

This is something we're investigating for future improvements. So if you could provide some details on what you would like this login to be able to do and what you don't want the login to see or do, it could help us better solve your problems as we design future versions of SQL Server. Also whether you use SQL Logins or Windows Integrated Logins - how many different logins you'd like to secure in this way, and anything else you think might be relevant.

Thanks,
Jack Richins
SDE SQL Server

|||

Dear Jack,

This whole issue took me by surprise. In our shop we have three teams of developers and all the development datbases are in a single sql instance. One of the group leaders asked me if there is any way to prevent those not in his group from even seeing the database display name under SSMS and sql tools (e.g. query editor, via OLEDB). I don't know the reason for his concern -- developers outside his group are not authorized to even view his databases but from a security perspective it seems reasonable: If a user does not have access to a databse, why should the database even "exist" for that user. This applies regardless of the form of Windows authentication used.

TIA,

Barkingdog

|||

Thanks, this isn't a scenario we'd been discussing so I'm glad you brought this up. I'm sorry we don't have a solution for you now, but hopefully in the future we can do something to enable this.

Thanks,
Jack Richins
SDE SQL Server

|||Although it blocks users from seeing other databases in the SMSS database dropdown, as the user I can still query sys.databases and see everything and I have used the deny view any database to public. In a hosting environment, this just won't be acceptable. In sql 2000, I just created a sysdatabases view that looked at your credentials and passed what you had rights to. Can't do that in 2005, but definately need this.|||

The main issue here is that determining what databases a user can see is an expensive check, as the permissions to access the database are stored in the database itself, so verifying them requires a database access. This prevents a general solution from being available through SSMS - some customers manage hundreds of databases on a single server, which would make such checks prohibitive.

You can still write a view in SQL Server 2005 that could filter rows using "has_dbaccess() = 1" as a predicate, but you should only do this if you have a limited number of databases, as it would be too expensive otherwise.

I also want to add that we're aware of this request and we're looking at ways to address it.

Thanks
Laurentiu

Hide database names from unauthroized users in SSMS

If a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user

TIA,

Barkingdog

Hi,

you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I found the server-level permission "View any database", denied it to the test user, and found exactly that. The user couldn't see (under SSMS) any databases whatsoever including the one he is the DBO of! How can I assign him permissions to see that one database but no others in the SSMS tree?

TIA,

Barkingdog

P.S. The evidence is if I removed that permission from "public" then no one -- maybe even the sa? - could see any databases in SSMS.!

|||

How are you making the user an owner? Are you making him a member of db_owner or are you using the alter authorization statement to make the user the actual owner of the database?

You can tell by looking at the owner_sid in sys.databases - the sid should point to the owning login. If the login is the owner of the database, denying that user view any database will not prevent him from viewing the database in sys.databases or Management Studio.

Jack Richins
SDE Sql Server

|||

I made him a member of the db_owner.

When I run

sp_helpuser <DBName>

it returns x01 as the SID for the username dbo.

When I run

use DBName

select * from sys.databases

it returns.......

0x010500000000000515000000AAB950D6A501....

for the owner sid of the database.

So the user is not the DBO (though he belongs to that role).

Then back to my original question. How can I deny a user from seeing any databses in the SSMS tree that he does not have permission to access?

TIA,

Barkingdog

|||

DENY VIEW ANY DATABASE to the test login. This prevents the user from seeing any databases for which the user is not the DBO. You can make the user a DBO by using ALTER AUTHORIZATION DATABASE::<database> TO <login>. Being a member of db_owner is not sufficient to see the database if "view any database" was denied.

This is not very clean and may not work for you if you can't make this user login the actual DBO of the database you want him to administer or have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.

This is something we're investigating for future improvements. So if you could provide some details on what you would like this login to be able to do and what you don't want the login to see or do, it could help us better solve your problems as we design future versions of SQL Server. Also whether you use SQL Logins or Windows Integrated Logins - how many different logins you'd like to secure in this way, and anything else you think might be relevant.

Thanks,
Jack Richins
SDE SQL Server

|||

Dear Jack,

This whole issue took me by surprise. In our shop we have three teams of developers and all the development datbases are in a single sql instance. One of the group leaders asked me if there is any way to prevent those not in his group from even seeing the database display name under SSMS and sql tools (e.g. query editor, via OLEDB). I don't know the reason for his concern -- developers outside his group are not authorized to even view his databases but from a security perspective it seems reasonable: If a user does not have access to a databse, why should the database even "exist" for that user. This applies regardless of the form of Windows authentication used.

TIA,

Barkingdog

|||

Thanks, this isn't a scenario we'd been discussing so I'm glad you brought this up. I'm sorry we don't have a solution for you now, but hopefully in the future we can do something to enable this.

Thanks,
Jack Richins
SDE SQL Server

|||Although it blocks users from seeing other databases in the SMSS database dropdown, as the user I can still query sys.databases and see everything and I have used the deny view any database to public. In a hosting environment, this just won't be acceptable. In sql 2000, I just created a sysdatabases view that looked at your credentials and passed what you had rights to. Can't do that in 2005, but definately need this.|||

The main issue here is that determining what databases a user can see is an expensive check, as the permissions to access the database are stored in the database itself, so verifying them requires a database access. This prevents a general solution from being available through SSMS - some customers manage hundreds of databases on a single server, which would make such checks prohibitive.

You can still write a view in SQL Server 2005 that could filter rows using "has_dbaccess() = 1" as a predicate, but you should only do this if you have a limited number of databases, as it would be too expensive otherwise.

I also want to add that we're aware of this request and we're looking at ways to address it.

Thanks
Laurentiu

|||I have tried changing the view any database property, which works, but the user cannot see the databases that they own... is there something else that needs to be done for them to see these databases and no others?|||

This is what I ended up doing:

--SQL 2005

If @.@.version like 'Microsoft SQL Server 2005%'

Begin -- Create Login only if it doesn't exist:

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @.Username)

Begin

Set @.dsql ='create login ' + @.Username + ' with password = N'''+ REPLACE(@.password, '''', '''''') + ''', DEFAULT_DATABASE=' + QUOTENAME(@.DBName) + ', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'

execute (@.dsql)

set @.dsql = 'use master; GRANT CONNECT SQL TO ' + QUOTENAME(@.DBName)

execute( @.dsql )

End

--Deny ability to see anything other than master, tempdb and user database.

Set @.dsql = 'use master; deny VIEW any DATABASE to ' + @.Username

--Print @.dsql

execute (@.dsql)

End

--Add user as dbo to database.

Set @.dsql = @.DBName + '.dbo.sp_changedbowner ' + @.Username

--Print @.dsql

execute (@.dsql)

But, I don't think you can grant multiple user's to DBO, so you will only have one user that can see master, temp, and their database.

-Chuck Lathrope

Hide database names from unauthroized users in SSMS

If a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user

TIA,

Barkingdog

Hi,

you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I found the server-level permission "View any database", denied it to the test user, and found exactly that. The user couldn't see (under SSMS) any databases whatsoever including the one he is the DBO of! How can I assign him permissions to see that one database but no others in the SSMS tree?

TIA,

Barkingdog

P.S. The evidence is if I removed that permission from "public" then no one -- maybe even the sa? - could see any databases in SSMS.!

|||

How are you making the user an owner? Are you making him a member of db_owner or are you using the alter authorization statement to make the user the actual owner of the database?

You can tell by looking at the owner_sid in sys.databases - the sid should point to the owning login. If the login is the owner of the database, denying that user view any database will not prevent him from viewing the database in sys.databases or Management Studio.

Jack Richins
SDE Sql Server

|||

I made him a member of the db_owner.

When I run

sp_helpuser <DBName>

it returns x01 as the SID for the username dbo.

When I run

use DBName

select * from sys.databases

it returns.......

0x010500000000000515000000AAB950D6A501....

for the owner sid of the database.

So the user is not the DBO (though he belongs to that role).

Then back to my original question. How can I deny a user from seeing any databses in the SSMS tree that he does not have permission to access?

TIA,

Barkingdog

|||

DENY VIEW ANY DATABASE to the test login. This prevents the user from seeing any databases for which the user is not the DBO. You can make the user a DBO by using ALTER AUTHORIZATION DATABASE::<database> TO <login>. Being a member of db_owner is not sufficient to see the database if "view any database" was denied.

This is not very clean and may not work for you if you can't make this user login the actual DBO of the database you want him to administer or have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.

This is something we're investigating for future improvements. So if you could provide some details on what you would like this login to be able to do and what you don't want the login to see or do, it could help us better solve your problems as we design future versions of SQL Server. Also whether you use SQL Logins or Windows Integrated Logins - how many different logins you'd like to secure in this way, and anything else you think might be relevant.

Thanks,
Jack Richins
SDE SQL Server

|||

Dear Jack,

This whole issue took me by surprise. In our shop we have three teams of developers and all the development datbases are in a single sql instance. One of the group leaders asked me if there is any way to prevent those not in his group from even seeing the database display name under SSMS and sql tools (e.g. query editor, via OLEDB). I don't know the reason for his concern -- developers outside his group are not authorized to even view his databases but from a security perspective it seems reasonable: If a user does not have access to a databse, why should the database even "exist" for that user. This applies regardless of the form of Windows authentication used.

TIA,

Barkingdog

|||

Thanks, this isn't a scenario we'd been discussing so I'm glad you brought this up. I'm sorry we don't have a solution for you now, but hopefully in the future we can do something to enable this.

Thanks,
Jack Richins
SDE SQL Server

|||Although it blocks users from seeing other databases in the SMSS database dropdown, as the user I can still query sys.databases and see everything and I have used the deny view any database to public. In a hosting environment, this just won't be acceptable. In sql 2000, I just created a sysdatabases view that looked at your credentials and passed what you had rights to. Can't do that in 2005, but definately need this.|||

The main issue here is that determining what databases a user can see is an expensive check, as the permissions to access the database are stored in the database itself, so verifying them requires a database access. This prevents a general solution from being available through SSMS - some customers manage hundreds of databases on a single server, which would make such checks prohibitive.

You can still write a view in SQL Server 2005 that could filter rows using "has_dbaccess() = 1" as a predicate, but you should only do this if you have a limited number of databases, as it would be too expensive otherwise.

I also want to add that we're aware of this request and we're looking at ways to address it.

Thanks
Laurentiu

Hide Certain reports from users

Hi All,
Is it any way to hide a particular report from listing in the report folder?
My actual problem is I deployed 2 reports in the report server and one of
them is a subreport. And I want the users should able to run only the main
report and not the subreport.
TIA,
SamuelIn report manager go to the properties page, the general sub tab there is a
check box for 'Hide in List View'. When a users browses to that folder in
the report manger (using list view, not detail view) then they will not see
that item.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ui1uXhybEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> Is it any way to hide a particular report from listing in the report
folder?
> My actual problem is I deployed 2 reports in the report server and one of
> them is a subreport. And I want the users should able to run only the main
> report and not the subreport.
> TIA,
> Samuel
>|||Hi Daniel,
I already tried that option too. But I want to restrict the user in both
views. Is any way to hide reports in details view?
Or Can I deploy subreport in a different directory and refer it in the main
report?
Thanks,
Samuel
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23nz1AO0bEHA.2944@.TK2MSFTNGP11.phx.gbl...
> In report manager go to the properties page, the general sub tab there is
a
> check box for 'Hide in List View'. When a users browses to that folder in
> the report manger (using list view, not detail view) then they will not
see
> that item.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Samuel" <samuel@.photoninfotech.com> wrote in message
> news:ui1uXhybEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Hi All,
> >
> > Is it any way to hide a particular report from listing in the report
> folder?
> > My actual problem is I deployed 2 reports in the report server and one
of
> > them is a subreport. And I want the users should able to run only the
main
> > report and not the subreport.
> >
> > TIA,
> > Samuel
> >
> >
>|||I think you can put the subreport in a sub directory. However the directory
will still be visible to users. There is no way to completly hide the
report outside of using permissions, but then the users could not run the
report.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ejH7Eu6bEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hi Daniel,
> I already tried that option too. But I want to restrict the user in both
> views. Is any way to hide reports in details view?
> Or Can I deploy subreport in a different directory and refer it in the
main
> report?
> Thanks,
> Samuel
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23nz1AO0bEHA.2944@.TK2MSFTNGP11.phx.gbl...
> > In report manager go to the properties page, the general sub tab there
is
> a
> > check box for 'Hide in List View'. When a users browses to that folder
in
> > the report manger (using list view, not detail view) then they will not
> see
> > that item.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Samuel" <samuel@.photoninfotech.com> wrote in message
> > news:ui1uXhybEHA.384@.TK2MSFTNGP10.phx.gbl...
> > > Hi All,
> > >
> > > Is it any way to hide a particular report from listing in the report
> > folder?
> > > My actual problem is I deployed 2 reports in the report server and one
> of
> > > them is a subreport. And I want the users should able to run only the
> main
> > > report and not the subreport.
> > >
> > > TIA,
> > > Samuel
> > >
> > >
> >
> >
>

Hide Calculations for some users

Hi,

anyone know if it is possible to secure some calculated measures from different users?

I have a calculated field in the cube that I just want to show for one user.

I guess I need to write some MDX in the "Allow reading of cube content" under Edit Cell data when I edit the role.

Someone have any suggestions?

I found a solution! =)

If you want to hide the measure TB this is the code that you write in the "Allow reading of cube content" under Cell Data.

Code Snippet

Not Measures.CurrentMember IS Measures.TB

Hide Calculations for some users

Hi,

anyone know if it is possible to secure some calculated measures from different users?

I have a calculated field in the cube that I just want to show for one user.

I guess I need to write some MDX in the "Allow reading of cube content" under Edit Cell data when I edit the role.

Someone have any suggestions?

I found a solution! =)

If you want to hide the measure TB this is the code that you write in the "Allow reading of cube content" under Cell Data.

Code Snippet

Not Measures.CurrentMember IS Measures.TB

sql

Hide Breadcrumbs

My users access SQL reports via weblinks from a Sharepoint portal. Even
though they don't have backward access through the folders, they can still
hee the breadcrumbs. How do I hide the breadcrumbs to the folder heirarchy?
Thanks for the help
JDJD,
It sounds like you are copying the URL out of Internet Explorer and
making it the source of web part in SharePoint. There are actually
SharePoint web parts that have been provided by Microsoft for this. For
more info, go to http://msdn2.microsoft.com/en-us/library/ms159772.aspx
If you want to keep using URLs, you can use the ReportViewer control to
open the report instead of opening a Report Manager URL. The Report
Viewer control does not show the report WITHIN Report Manager, so there
are no breadcrumbs.
With:
server name = goodwin
Report Server virtual directory = reportserver
report path = Production/Reports/
report name = Report1
The URL would look like this:
http://goodwin/reportserver/Pages/ReportViewer.aspx?/Production/Reports/Report1
To modify toolbar options, you can use some ReportViewer parameters.
http://msdn2.microsoft.com/en-us/library/ms152835.aspx
Hope this helps!
-Josh
JDArsenault wrote:
> My users access SQL reports via weblinks from a Sharepoint portal. Even
> though they don't have backward access through the folders, they can still
> hee the breadcrumbs. How do I hide the breadcrumbs to the folder heirarchy?
> Thanks for the help
> JD|||Josh,
Just what the doctor ordered!
Thanks
JD
"Josh" wrote:
> JD,
> It sounds like you are copying the URL out of Internet Explorer and
> making it the source of web part in SharePoint. There are actually
> SharePoint web parts that have been provided by Microsoft for this. For
> more info, go to http://msdn2.microsoft.com/en-us/library/ms159772.aspx
> If you want to keep using URLs, you can use the ReportViewer control to
> open the report instead of opening a Report Manager URL. The Report
> Viewer control does not show the report WITHIN Report Manager, so there
> are no breadcrumbs.
> With:
> server name = goodwin
> Report Server virtual directory = reportserver
> report path = Production/Reports/
> report name = Report1
> The URL would look like this:
> http://goodwin/reportserver/Pages/ReportViewer.aspx?/Production/Reports/Report1
> To modify toolbar options, you can use some ReportViewer parameters.
> http://msdn2.microsoft.com/en-us/library/ms152835.aspx
> Hope this helps!
> -Josh
>
> JDArsenault wrote:
> > My users access SQL reports via weblinks from a Sharepoint portal. Even
> > though they don't have backward access through the folders, they can still
> > hee the breadcrumbs. How do I hide the breadcrumbs to the folder heirarchy?
> >
> > Thanks for the help
> >
> > JD
>

Friday, March 23, 2012

Hidden columns showing on export

I am allowing users to specify through a parameter whether they want a column
to be visible or not. If they specify visible = false, the column does not
show on the report. However, when that same report is exported, the column is
visible in the export.
Does anyone have a solution for this?Hello.
I am having a similar problem. I'm trying to make some table columns hidden
(collapsed). The regular online report view is fine and the hidden columns
are hidden and propery collapsed. However, when I export (to pdf) the hidden
columns are blank but not collapsed, except for the table footer row, so the
table footer is misaligned with the rest of the table. The columns in the
table footer row collapsed propery, but the rest of the table's hidden
columns are blank but did not collapse. So I end up with a table with more
header and detail columns than footer columns.
Any idea what is happening?
Thanks,
Brian.
"khonerka" wrote:
> I am allowing users to specify through a parameter whether they want a column
> to be visible or not. If they specify visible = false, the column does not
> show on the report. However, when that same report is exported, the column is
> visible in the export.
> Does anyone have a solution for this?

Wednesday, March 7, 2012

Help:Locking...

Hello .
I am using SQL Server 2000 in order to create a multi user program that accesses data.
The problem is that multiple users will update and select data at the same time at the same table.

Is there a way to avoid deadlocks ?
I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update.
and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.

does anybody know much about these ways? do you know where i can find code for this ?

is there a better way?

thanks !
and happy new year !yeah, according to the textbook of database, there should be 2 other ways instead of write the information to other place from the database.

1. lock all what u will lock in the transaction when u begin the transaction.

2. all kinds of resources should be locked at the same order.

hehe.

happy new year.|||Those two ways you described will help but truely it all depends on how you write your sql script. Are all the updates and deletes using cursors? Do you have begin trans and commit on all your transactions? There is no one way you can completely avoid deadlocks, but there are ways to manage it. The best way is to let us know what sql script in your environment that creates deadlock so we can better help with your situations.|||Are we talking about deadlocks or blocking here? It surely sounds that the poster is concerned about object availability when more than one connection attempts to access its data.|||There is no silver bullet to avoid deadlocking other than fully analysing the transactions you intend to commit. Using a temporary table may work, as long as your transaction does not depend upon data previously read from the permanent tables remaining unchanged during the accretion of the temporary data. XML in this regard is a complete red herring.

Essentially you control locking policy using the SET TRANSACTION ISOLATION LEVEL command and wrapping all calls with an outer transaction until all components of the unit of work are available for committing.

You need to consider whether the integrity of data reads is essential to the integrity of the intended data writes. If youre booking seats on an aircraft you must ensure no other user books your particular seat between you reading that its free and you writing that youre booking it. To make reads fully transactional with writes you use isolation level SERIALIZABLE everything you read is locked against being updated or inserted against for the duration of the transaction. Isolation level REPEATABLE READ ensures that data you have read cannot be updated by another user, but allows background inserts.

If it is not possible to extend the duration of the transaction between critical reads (i.e. you can keep your option on the flight seat open for twenty minutes) you must implement soft locking of the seat row in the permanent tables using something like a timestamp or a flag.

Isolation level READ COMMITTED ensures you can only read data that has had full transactional commit to the database. You use this read mode to ensure you never read over another users half finished work, but you have no locks of the data you have read for the purposes of subsequent writes. READ UNCOMMITTED means you read through any locks applied during any other users writes. You cannot write through any other users locks.

Particularly useful in ensuring transactional integrity is SET XACT_ABORT ON, which will cause any error anywhere in your SQL to Rollback the entire transaction.

Also be aware that if a deadlock does occur SQL will terminate one or other transaction, at random, unless you explicitly set a deadlock priority on the thread whose death you would prefer to occur.|||Yeah, use strored procedures and stroe the data locally...

don't do dynamic sql

don't open recordsets...

Read data store

manipulate data in the app

when an action is to occur...update or delete, check the records timestamp to see if someone else alread modified the record and act accordingly

if ok, exec (through a sproc) your transaction...

keep'em short

Friday, February 24, 2012

HELP: Physical Database Design

Hello to all.

I'm having trouble translating this requirement into physical DB tables:

- Information about users in general are:

- Username and Password

- Users belong to a group which have different roles

- (e.g. Customers, Administrators)

- Users can only belong to one group

- Information about groups are:

- GroupID, GroupName, Roles

- Customers and Administrators have different fields

- Customers : (CustomerID, CompanyName, BillingAddress, ShippingAddress, etc.)

- Administrators: (AdministratorID, AdministratorName)

- The system must be able to do the following:

- Ban a group

- Ban a specific user

- Information about a user must be based on the group that he/she is in. (e.g. Customer info must be queried from the Customers table) with the username as the only provided information initially.

- Add a new user type in the future

Any comments and suggestions will do. Thanks in advance.

- Raymond

Here is a good resource for classwork design problems such as this.

If you review some of the designs listed at this site, you will have a better idea of how to accomplish your assignment. (Note: the models and designs at this site are only a suggestion and should be used with caution -but they are better than nothing...)

http://www.databaseanswers.org/data_models/index.htm