Accessing domain user name as a report parameter

In our legacy reports (ASP.NET/Crystal Reports) we use the logged in user account to filter the report.
Basically you are restricted to seeing your 'own' data when running the report based upon your user ID.
I am looking for suggestions as how to restrict report results based on something like domain user ID in Reporting Services.
There are many cases (like in the sales profession) where users of the same report do not want other users to access their information.
Thanks for any suggestions.use the global variable User!UserID
be aware that you may not create subscriptions for a report that contains user-defined parameter values.

Accessing database from User Defined Aggregate

Hi,
I am trying to access the database (using SqlConnection and then SQLCommand)
from within an CLR User Defined Aggregate, but SQL 2005 always return that
"Data access is not allowed in this context".
Anybody knows how can I do this?
Thanks,
FernandoAFAIK, you cannot do this.
What is your need to do this?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:6025EC30-71C4-4BD7-B96C-FE62C98A33D9@.microsoft.com...
> Hi,
> I am trying to access the database (using SqlConnection and then
> SQLCommand)
> from within an CLR User Defined Aggregate, but SQL 2005 always return that
> "Data access is not allowed in this context".
> Anybody knows how can I do this?
> Thanks,
> Fernando|||SriSamp,
Thanks for your response. While doing aggregation I may need to access
additional data for computing my calculation.
It is not a trivial aggregations (sum, avg, etc.). This is specific for some
statistics calculation for non-linear equations.
Thanks,
Custodio
"SriSamp" wrote:
> AFAIK, you cannot do this.
> What is your need to do this?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Fernando" <Fernando@.discussions.microsoft.com> wrote in message
> news:6025EC30-71C4-4BD7-B96C-FE62C98A33D9@.microsoft.com...

Accessing cubes parallely from 2 clients

Hello,

When 2 logins try to access a cube(in MSAS 2000), we are getting the following error message,

"This database is locked. User (....) on computer (....) has locked (....). Description : Cube edotir has locked cube (....) for writting."

Is there anyway we can access a cube simultaneously from 2 client machines (read only type of access)?

Thanks in advance....

Revin

Hi Revin,

Is the Analysis Manager open and modifying the cube structure at the same time. If so, close the Analysis Manager and try again. It will place a lock on the repository file and will not allow other connections.

If this doesn't work then stop and start the Analysis Server to free up any locks that are hanging around.

Hope this helps,

David

|||

Thanks David!!!

Yes the analysis service is open at 2 clients.

What i would like to know is that, is there any way i can access (read only type) the cube while another user is editing the cube in devlopment environment?

Thanks,

Revin

Accessing ASPNETDB from outside web application

Hi there,

I have a slight problem. I have a web service running on my server. In this, I need to access values in User's profiles. The problem is of course, that Profile, ProfileCommon, ProfileBase, etc... are not available to me here.

What can I do?

Is using an SQL command best? If so, could someone post an example SQL command - I am getting confused by how to structure the command, since I need Membership data like UserName, and Profile data (an object serialized as binary in the Profile).

Kind regards,

kreid

Hi Kreid,

To share the ASPNETDB to another application, you will need to attach the database to an instance of SQL Server. Then change the connection string of both applications to point to that database.

|||

Hi Kevin,

I'm having the sampe problem even though I have my ASPNETDB in an SQL-server. After thoroughly checking the interfaces, and forums, there is apparently no way to access the Profile from a console application. Certainly, I can do a LINQ query and get the Profile properties string and values string from the ASPNETDB, but then parse them myself etc.? There must be a better way? Any tips/examples out there?

Thanks!

Richard

|||

The Solution:

http://blogs.msdn.com/msdnts/archive/2006/12/16/asp-net-2-0-membership-role-management-out-of-asp-net-context.aspx

Accessing AS 2005 from ASP.NET application

I have an asp.net application. This application provide consolidated information to the user using Analysis Services 2005.
Within it, one aspx page uses MDX query to retrieve the information from the cube. However, we are unable to access AS 2005 because of authentication issues. What would be the easier to implement and deploy scenario?

How can I access AS 2005 from an ASP.NET application? Is there any feature to use?

Create Views and CTE( common table expressions ) with the results of the MDX queries and render the Views and CTEs in DataGridview controls. That means you are just calling Views query rewrites one persisted and the other in memory or virtual views in Asp.net. This keeps Analysis Service operation out of the Asp.net page life cycle. Hope this helps.

Accessing analysis services from asp.net application

I have an asp.net application. This application provide consolidated information to the user using Analysis Services 2005.
Within it, one aspx page uses MDX query to retrieve the information from the cube. However, we are unable to access AS 2005 because of authentication issues. What would be the easier to implement and deploy scenario?
1) Using kerberos
2) creating a webservice with some Identity to access AS 2005 and using an agent in the application;
3) some other mechanism.

Has AS 2005 come up with some mechanism to allow easy access from web applications (just like sql server authentication in SQL 2005). Is there any feature that we could use? What about anonymous access?

The closest thing we have with AS and sql logins, is http access. Using http access you can set a uid and pwd on the connect string and then the system will use those to authenitcate you against IIS (where the AS server is). This allows your ASP.NET application to use a canned account to access AS. See the connection string parameters allowed on ADOMD.NET (which is what you should be using as a managed provider for your ASP.NET application).

_-_-_ Dave

|||And how do you implement AS role-based security in this scenario?
If you use a canned account to access AS, you cannot match you users with an AS role.
Am I missing something ?|||You didn't say that you wanted to use role-based security, so I gave you a general answer. There are two issues here:

1) getting basic connectivity. If the IIS / ASP.NET machine is in a common domain then you can connect using normal NT authentication (run the virtual directory anonymously and do your own authentication within the application) -- have the anonymouse user for the vd to be a common domain account with the AS server and ensure that it is an Analysis Services sysadmin. If not in a common domain, then use http connectivity and specify an NT UID/PWD which is an AS sysadmin.

2) Once you are connected as an AS sysadmin, then you can use the new EFFECTIVEUSERNAME to set the appropriate username for the connection. Then BINGO there you are.

Hope that helps.

_-_-_ Dave

Accessing #temp table in a proc as a user with minimal rights.

I need to create a #temp table in a proc as a user with minimal rights and t
hen
insert into it and select from it.
However, I am getting an error telling me that either the table does not exi
st or I
do not have sufficient rights to access it.
How do I get around this rights problem?
Thank you.
MikeMike,
Can we see the code?
AMB
"Mike Malter" wrote:

> I need to create a #temp table in a proc as a user with minimal rights and
then
> insert into it and select from it.
> However, I am getting an error telling me that either the table does not e
xist or I
> do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>
>|||YOu have to post soe DDL for us to see the error.
HTH (us), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
>then insert into it and select from it.
> However, I am getting an error telling me that either the table does not
> exist or I do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Anyone can create temp tables. Post use the code, or code to reproduce the p
roblem and we can have a
look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
then insert into it and
>select from it.
> However, I am getting an error telling me that either the table does not e
xist or I do not have
> sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Guys,
This was my goof.
When I was in the process of reviewing the code I was going to put up here,
I
realized that instead of creating the table and then inserting from a select
. I was
creating the table and then selected into. Which you can't do if the table
already
exists.
Sorry for bothering you guys, and thank everyone for their willingness to ju
mp in and
help.
Best regards.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
> Mike,
> Can we see the code?
>
> AMB
> "Mike Malter" wrote:
>|||That is what SQL Community is for :-))
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:e8BTHwRRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Guys,
> This was my goof.
> When I was in the process of reviewing the code I was going to put up
> here, I realized that instead of creating the table and then inserting
> from a select. I was creating the table and then selected into. Which
> you can't do if the table already exists.
> Sorry for bothering you guys, and thank everyone for their willingness to
> jump in and help.
> Best regards.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
>

Access user and system DSNs programmatically

Just as the ODBC Data Source Administrator lists user and system DSNs, I wan
t
to access the same information programmatically in C#. I found registry
entries for these but I was hoping there was a higher-level routine that
would provide the information. The OdbcFactory.CreateDataSourceEnumerator
looked promising at first, but it seems to list just instances of SqlServer.
Is there a .NET method that could provide this information?
If I have to use a registry lookup, how do I determine the path for the
current user in the registry?Hi,
I understand that you would like to know how to list all the DSNs installed
on a computer programmatically in C#.
If I have misunderstood, please let me know.
I recommend that you utilize either of the following two methods:
1. Use P-Invoke in C# to call SQLDataSources.
You may refer to the following articles:
SQLDataSources(ODBC32)
http://www.pinvoke.net/default.aspx...ataSources.html
Listing Available DSN / Drivers Installed
http://www.codeguru.com/vb/gen/vb_d...icle.php/c2045/
2. Write the code to analyze the following information respectively:
System DSNs are stored in the registry at:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
User DSNs are stored in ODBC.INI file that is located at:
C:\WINNT
File DSNs are stored in the following directory:
C:\Program Files\Common Files\ODBC\Data Sources
Note that this response contains a reference to a third party World Wide
Web site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.
If you have any other questions or concerns, please feel free to let me
know. Happy New Year!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||I tried to incorporate from the code samples you pointed to, but I did not
know how to properly reference the function calls (i.e. SQLDataSources,
SQLSetEnvAttr, SQLAllocHandle) that are apparently from the odbc32.dll.
However, I did manage to find a simple implementation of accessing the info
from the registry at http://www.thescripts.com/forum/thread269514.html. I
refactored the code from that thread into this short solution:
public static SortedList listAllDSN()
{
SortedList allDSN = new SortedList();
// Get User DNS Names
GetOdbcRegistryValues(allDSN, Registry.CurrentUser);
// Get System DNS Names
GetOdbcRegistryValues(allDSN, Registry.LocalMachine);
return allDSN;
}
private static void GetOdbcRegistryValues(SortedList allDSN, RegistryKey reg
)
{
reg = reg.OpenSubKey("Software");
reg = reg.OpenSubKey("ODBC");
reg = reg.OpenSubKey("ODBC.INI");
reg = reg.OpenSubKey("ODBC Data Sources");
if (reg != null)
{
foreach (string s in reg.GetValueNames())
{
try { allDSN.Add(s, null); }
catch { }
}
}
try { reg.Close(); }
catch { }
}|||Hi,
Thanks for your response.
SQLDataSources, SQLSetEnvAttr and SQLAllocHandle are indeed included in
odbc32.dll. That is why you need to use P/Invoke technology in C#/VB.NET to
call them. You need to re-declare them in C#. The first link should have
introduced how to invoke it:
//1. import it and redeclare it in C#
[DllImport("odbc32.dll", CharSet=CharSet.Ansi))]
static extern short SQLDataSources(IntPtr EnvironmentHandle, short
Direction,
StringBuilder ServerName, short BufferLength1, ref short
NameLength1Ptr,
StringBuilder Description, short BufferLength2, ref short
NameLength2Ptr);
//2. Call it directly
....
rc = SQLDataSources(sql_env_handle, SQL_FETCH_FIRST, dsn_name,
(short)dsn_name.Capacity, ref dsn_name_len, desc_name, (short)
desc_name.Capacity, ref desc_len);
For SQLAllocHandle and SQLSetEnvAttr, you can find them in the web site:
http://www.pinvoke.net/default.aspx...llocHandle.html
http://www.pinvoke.net/default.aspx...SetEnvAttr.html
Anyway I am glad to hear that you had found the resolution by yourself. If
you have any other questions or concerns, please feel free to let me know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Access user and system DSNs programmatically

Just as the ODBC Data Source Administrator lists user and system DSNs, I want
to access the same information programmatically in C#. I found registry
entries for these but I was hoping there was a higher-level routine that
would provide the information. The OdbcFactory.CreateDataSourceEnumerator
looked promising at first, but it seems to list just instances of SqlServer.
Is there a .NET method that could provide this information?
If I have to use a registry lookup, how do I determine the path for the
current user in the registry?
Hi,
I understand that you would like to know how to list all the DSNs installed
on a computer programmatically in C#.
If I have misunderstood, please let me know.
I recommend that you utilize either of the following two methods:
1. Use P-Invoke in C# to call SQLDataSources.
You may refer to the following articles:
SQLDataSources(ODBC32)
http://www.pinvoke.net/default.aspx/odbc32/SQLDataSources.html
Listing Available DSN / Drivers Installed
http://www.codeguru.com/vb/gen/vb_database/article.php/c2045/
2. Write the code to analyze the following information respectively:
System DSNs are stored in the registry at:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
User DSNs are stored in ODBC.INI file that is located at:
C:\WINNT
File DSNs are stored in the following directory:
C:\Program Files\Common Files\ODBC\Data Sources
Note that this response contains a reference to a third party World Wide
Web site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.
If you have any other questions or concerns, please feel free to let me
know. Happy New Year!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||I tried to incorporate from the code samples you pointed to, but I did not
know how to properly reference the function calls (i.e. SQLDataSources,
SQLSetEnvAttr, SQLAllocHandle) that are apparently from the odbc32.dll.
However, I did manage to find a simple implementation of accessing the info
from the registry at http://www.thescripts.com/forum/thread269514.html. I
refactored the code from that thread into this short solution:
public static SortedList listAllDSN()
{
SortedList allDSN = new SortedList();
// Get User DNS Names
GetOdbcRegistryValues(allDSN, Registry.CurrentUser);
// Get System DNS Names
GetOdbcRegistryValues(allDSN, Registry.LocalMachine);
return allDSN;
}
private static void GetOdbcRegistryValues(SortedList allDSN, RegistryKey reg)
{
reg = reg.OpenSubKey("Software");
reg = reg.OpenSubKey("ODBC");
reg = reg.OpenSubKey("ODBC.INI");
reg = reg.OpenSubKey("ODBC Data Sources");
if (reg != null)
{
foreach (string s in reg.GetValueNames())
{
try { allDSN.Add(s, null); }
catch { }
}
}
try { reg.Close(); }
catch { }
}
|||Hi,
Thanks for your response.
SQLDataSources, SQLSetEnvAttr and SQLAllocHandle are indeed included in
odbc32.dll. That is why you need to use P/Invoke technology in C#/VB.NET to
call them. You need to re-declare them in C#. The first link should have
introduced how to invoke it:
//1. import it and redeclare it in C#
[DllImport("odbc32.dll", CharSet=CharSet.Ansi))]
static extern short SQLDataSources(IntPtr EnvironmentHandle, short
Direction,
StringBuilder ServerName, short BufferLength1, ref short
NameLength1Ptr,
StringBuilder Description, short BufferLength2, ref short
NameLength2Ptr);
//2. Call it directly
.....
rc = SQLDataSources(sql_env_handle, SQL_FETCH_FIRST, dsn_name,
(short)dsn_name.Capacity, ref dsn_name_len, desc_name, (short)
desc_name.Capacity, ref desc_len);
For SQLAllocHandle and SQLSetEnvAttr, you can find them in the web site:
http://www.pinvoke.net/default.aspx/odbc32/SQLAllocHandle.html
http://www.pinvoke.net/default.aspx/odbc32/SQLSetEnvAttr.html
Anyway I am glad to hear that you had found the resolution by yourself. If
you have any other questions or concerns, please feel free to let me know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Access to tables SQL Server 2005

Hello!

I have a application where users are supposed to edit data from a table in a datagrid.

I want to use a dropdownlist to let the user choose a table to edit.

The users are members of different windows-goupes and different users have only access to edit there own tables. Let's say that they own data in different tables.

Is it possible to use windows authentication to find out what tables to show in the dropdownlist?

If it is, how do I get the names of the tables?

Best regards

Per

This query 'should' provide a list of ONLY the tables whereon the user has at least SELECT permission.

Code Snippet

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

|||

Thank you!

That is very useful.

//P

access to sqlserver database from web

Hi
I do not want to install SQL server client on every user machine.
I want to give users access from browser to the sqlserver 2000 database.
Is it possible?
I saw sqlserver webadministrator?
From the name it looks like a administration tool?
Thanks
Mangesh
"SQL Server Web Data Administrator"
http://www.microsoft.com/downloads/d...displaylang=en
Cristian Lefter, SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:8B328C00-790E-4FD0-82F3-022F3C6ABA40@.microsoft.com...
> Hi
> I do not want to install SQL server client on every user machine.
> I want to give users access from browser to the sqlserver 2000 database.
> Is it possible?
> I saw sqlserver webadministrator?
> From the name it looks like a administration tool?
> Thanks
> Mangesh

access to sqlserver database from web

Hi
I do not want to install SQL server client on every user machine.
I want to give users access from browser to the sqlserver 2000 database.
Is it possible?
I saw sqlserver webadministrator?
From the name it looks like a administration tool'
Thanks
Mangesh"SQL Server Web Data Administrator"
http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en
Cristian Lefter, SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:8B328C00-790E-4FD0-82F3-022F3C6ABA40@.microsoft.com...
> Hi
> I do not want to install SQL server client on every user machine.
> I want to give users access from browser to the sqlserver 2000 database.
> Is it possible?
> I saw sqlserver webadministrator?
> From the name it looks like a administration tool'
> Thanks
> Mangesh

access to sqlserver database from web

Hi
I do not want to install SQL server client on every user machine.
I want to give users access from browser to the sqlserver 2000 database.
Is it possible?
I saw sqlserver webadministrator?
From the name it looks like a administration tool'
Thanks
Mangesh"SQL Server Web Data Administrator"
http://www.microsoft.com/downloads/...&displaylang=en
Cristian Lefter, SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:8B328C00-790E-4FD0-82F3-022F3C6ABA40@.microsoft.com...
> Hi
> I do not want to install SQL server client on every user machine.
> I want to give users access from browser to the sqlserver 2000 database.
> Is it possible?
> I saw sqlserver webadministrator?
> From the name it looks like a administration tool'
> Thanks
> Mangesh

Access to SQL Migration

At work we are migrating several Access databases to SQL Server 2005 standard edition. These Access databases have no user interface forms. They do however have a series of macros that execute make table queries.

I'm trying to determine what are options are for replacing the make table queries in the macros. My first thought was to do the work in a SQL Server stored prodecure. I am asking for advice on what our options are. Is a stored procedure the way to go? Any suggestions on how to do it?

Thanks.

Stored Procedures and Views are probably your best bet. Have you tried using the SQL Server Migration Assistant for Access?|||Thanks for your answer Euan.

Do you know if stored procedures can be scheduled to run? I believe SQL Server has some sort of job scheduler.

Thanks.|||Yes, you can schedule SQL Server stored procedures to run as a job. You can specify the owner of the job, the frequency and the task done by the job - in this case, run a stored procedure|||Thanks bass_player.|||One thing to note, there is no scheduler as such in Express, you need to use the windows scheduler and then have it call sqlcmd (command line script utility) to perform this

Access to SQL

Does anyone know how to import an Access db to SQL.

I need to do this programmatically.

The user selects the path to the Access.mdb clicks a button and the Access data and schema are imported to a SQL db.

I can do SQL to SQL with DTS.TransferObjectsTask2 and it works perfectly but I need to be able to do Access to SQL.

Thanks

You can do it in DTS package or SSIS package just select Access database in dts and ODBC data source in SSIS package. You can also use .Net and connect to Access using ODBC set of database objects and next save results into SQL using SQL objects you can use Dataset to store your tables or just read from ODBC and save into SQL

Thanks

access to data in the cube

Hi,

i'm currently building a cube which data has a level of access depending who the logged in user is in the windows authentication. My cube's fact table is actually a VIEW with this line of code:

SELECT a.serviceid AS [service code], a.agentid AS [agent code], a.timeid AS date, a.talktime AS [talk time], a.wraptime AS [wrap time]
FROM dbo.factAgentActivity AS a INNER JOIN
dbo.factaccess AS b ON a.agentid = b.agent AND b.username = SUSER_NAME()

I used SUSER_NAME to get the name of the user currently logged in and compared it against the username table. If i open this VIEW in the SQL SERVER database, i can retrieve the right data that user will see. But when I used this in the Analysis Services and Browse the cube, the metrics and cubes have blank values. The same thing if i link my cube to excel.

Is my setup correct or im missing something here?

thanks in advance!

regards,

cherriesh

I'm not sure to understand but:

"SUSER_NAME()" return the current user logged in.

It means that when you process the cube "SUSER_NAME()" is replaced with the AS account.

If it is correct, how can AS dynamically change the user on browser time?

Maybe I'm missing something.

Francesco

|||if you already know what access they should have, why wouldn't you just create a role(s) within your SSAS database project and assign the NT Users (or NT Groups) accordingly. Depending on your storage mode for your cube once you process the cube the view wouldn't work anyway, that would just be used to perform the initial load of the cube.

access tables without schema

Hello

in my database I have schema "x". I have user "x" who is database owner, The user has schema "x" as default schema and is owner of this schema.

Now there are tables "x.mytable"

If I connect to the db as "x" and try

select * from mytable

I get an error "Invalid object name", I have to write it this way

select * from x.mytable

Why?

Thank you

Eckard

You need to check the metadata for these tables and see what might be wrong. Here is a script that I put together to demonstrate that it works, with a few select statements peppered in that you can use to check your tables:

use tempdb
go
create user bob without login
go
grant create table to bob
go
create schema bob
go
ALTER AUTHORIZATION ON schema::bob TO bob
go
ALTER USER bob WITH DEFAULT_SCHEMA = bob
GO
--check defaults
select name,default_schema_name, type_desc
from sys.database_principals
where name = 'bob'
go
--Then switch context to executing as 'bob'
execute as user = 'bob'
go
--let him (er it) create a table, so it is in the schema
create table bob.tableName
(
tableNameId int
)
create table tableName2
(
tableNameId int
)

go
select name, schema_name(schema_id)
from sys.objects where name in( 'tableName2', 'tablename')
go
select *
from tableName
select *
from tableName2
go
revert

|||

Regarding I have user "x" who is database owner,

All users belonging to the database role db_owner will create by default objects in the dbo schema. So if you make a login X as the owner of a database, and then connect as that login to the database, the default schema for that user is the dbo schema. My guess would be that this is the problem you are running into. Instead of making the login 'X' , the owner of the database, why not grant him CONTROL permission on the database ?

|||

Thank you Louis, Asvin but it is still not working for me:

The user is named "gwg01", the table name is "caddr" and the dbname is "gwgdaten". I'm connected as "gwg01" when I execute this script

use gwgdaten

go

sp_change_users_login 'auto_fix', 'gwg01'

go

grant control to gwg01

go

create schema gwg01

go

ALTER AUTHORIZATION ON schema::gwg01 TO gwg01

go

ALTER USER gwg01 WITH DEFAULT_SCHEMA = gwg01

GO

select name,default_schema_name, type_desc

from sys.database_principals

where name = 'gwg01'

>>>> gwg01 gwg01 SQL_USER

select name, schema_name(schema_id)

from sys.objects where name in( 'CADDR')

>>>> CADDR gwg01

select * from caddr

>>>> Invalid object name 'caddr'.

Any ideas?

Thank you

Eckard

|||

Hi again

after I removed the user and created it new, everything works...

Eckard

Access Sql server using Javascript

Hi All,

I have a task need to update Sql server database whenever the user click "close" button in browser. It seemed I can't capture this event using asp.net. How can I access Sql server database in Javascript?
Thanks.

Quote:

Originally Posted by lindy

Hi All,

I have a task need to update Sql server database whenever the user click "close" button in browser. It seemed I can't capture this event using asp.net. How can I access Sql server database in Javascript?
Thanks.

Two Ideas for you:

1. Change your close button to use ASP.Net

2. Use AJAX to call a page that will update your information.

access SQL Server from application

Hi,

I have recently transfered data from MS Access to MS SQL Server 2000 and created user interface in MS Access Project (ADP). Now I need to secure the data on server and want to access it ONLY through interface I created in MS Access. Every user has to log in the application (user id and password). Users may have the same access rights to the server (so maybe just one account or whatever I need for all of them). Can anyone advice me how to set it up? To access sql server from MS Access I am using ADO.

Thank you.
djLookup "Application Roles" in Books Online.

But be aware that it is better practice to limit your to establish security at the data level than the application level. Establishing Application Roles is no substitute for making sure that your database is locked down.|||Yes, I agree. I have no experience with SQL Server. In MS Access it was easy. So, how to lock down database. What I should create there to secure data? Any password? And then I need to access data through ADO. There is connection string where I have to pass a password.

Could you help me to clarify that.
Thanks
dj

access rights - dynamic reports

hi,
is it possible to make "dynamic reports" for users with different user
rights. depending on their rights one user sees all the columns and
another one with less rights sees just the first column?
thanksthere probably is ... but since i can't tell you i'll give you an
alternative solution - why don't you just create 2 reports
one for general users containing data that general people can see
one for 'special' users containing all of the 'secret' data. because
more than likely once the 'special' users see what they can get,
they're going to want more and it'd be easier to manage the reports on
a group basis rather than a more granular column by column basis.
those are my thoughts anyway ...
hth!