can i grant all privilege to user?

it is possible to grant all privilege (CRUD) to specified table to user. But, now, i want to grant all privilege (CRUD) of all tables, views, sp, ... of database to the user. is it possible?

regards,

Yes you can try the link below for SQL Server object permissions. It can get complex but the link below can get you started. You have the option of doing it with T-SQL or with GUI with Management Studio. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

|||Now, i assign a role(db_owner) to the user. Because i don't know the different permissions of the different role, i just assign as db_owner. In my case, the only needed is DDL, CRUD DML, stored procedure. That's all. So, is there any other more suitable role. As i can't find the way that assign all these privilege only one command, i just assign a role. I don't know whether is it good or not.

Any idea?|||

You have two options dbcreator and db_ddladmin roles both are less than dbo and may do what you need because a of service pack3 in SQL Server 2000 dbcreator cannot change ownership of a database. The links below will help with more details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175892.aspx

http://msdn2.microsoft.com/en-us/library/ms189121.aspx

|||thx for ur kindness.|||I am glad I could help.sql

Can I filter the data on mining structure, mining model?

I perform data mining on all products and a specific product category.
Do I need to create 2 data source views, one for all products and the other one for the specific product category?
Afterward, I run the Data Mining Wizard 2 times to create 2 mining structures.
I also need to add the same mining model (e.g. Bayes, Cluster) to each of these mining structures.
Is there any simple way to do it?

Thanks.
Joe.

Yes you'd need to use two data sources. However instead of going through the wizard twice and recreating all the contained models, you could use SQL Mgmt Studio to script the mining structure and its contained models, modify the name in the generated script and then run t. To do this, right-click on the mining structure in SQL Mgmt Studio and select "Script Mining Structure As"-> CREATE TO.

|||Thanks Raman.

can i convert Crystal Reports to SRS 2005?

Hello,
Is it any way can i convert Crystal
Reports to SRS 2005? The data source in the Crystal
Reports I am using views from SQL Server 2000.http://msdn2.microsoft.com/en-us/library/aa964127(sql.90).aspx
Reeves
"GGill" wrote:
> Hello,
> Is it any way can i convert Crystal
> Reports to SRS 2005? The data source in the Crystal
> Reports I am using views from SQL Server 2000.
>|||Thanks.
"Reeves Smith" wrote:
> http://msdn2.microsoft.com/en-us/library/aa964127(sql.90).aspx
> Reeves
> "GGill" wrote:
> > Hello,
> > Is it any way can i convert Crystal
> > Reports to SRS 2005? The data source in the Crystal
> > Reports I am using views from SQL Server 2000.
> >

Can I automatically see which VIEWS are used for each REPORT?

Is there a way to see which database views (or tables) were used for each report?

(For example, I have a report called "Customer Oriented" and it uses 2 views: Customers, and Products. Can I automaticaly pull out this information?

Thanks,

The dataset information is just stored as text, unless you code your own logic for parsing that information will will have no chance of getting that information that you wanted to have.

HTH, Jens Suessmeyer.

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

Hi Jens,

Where can I see that text? (the one in which you can see the name of dataset). Although I am more looking for seeing database VIEWS or TABLES used to create a report rather than a DATASET, but that might give me some insight.

Thank you.

Alexan

|||

You can call GetReportDefinition() on the SOAP API for a published report and retrieve the dataset elements from the report. You would then have to parse the contents of the dataset manually to determine the names of the tables or views which are referenced.

Out of curiosity, why do you have this requirement?

|||The dataset definition is presented as a node in the XML data (WHat John ment with Report Definition), just can just retrieve that with e.g. a method like SelectSingleNode.

HTH, Jens Suessmeyer.

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

Thanks for your answers. Your answers are still very higher than my knowledge and I don't know how to do them.

John: The reason that I need to do this is because we need to change the Views from time to time (to satisfy newer reports needs) so we want to see what reports that we have already made from a particular view might be affected by these changes. I think this is a very lame way, and it's better to create a new view for each specific report, but my boss says maintaining a lot of views is pain for him. I am not sure what he meant.

|||

He meant that it is better for you to have pain than for him to have it.
(There are lots of times I am all shades of wrong.)

R

|||

In all honesty you shouldn't try to do this from Reporting Services. The data entered in the query box is any valid statement that will return a result set. Considering just SQL server as a data source, valid statements include stored procedure and table valued function calls and trying to parse the SQL to extract table names is crazy.

You should perform an audit and store and maintain that information in some spreadsheet.

That's my 2 cents.

|||

Think you're right. Thank you all.

Can data be queried in OLAP cubes as in Views?

This question may appear a bit pedestrian but nonetheless.

We are in the process of building OLAP cubes for data analysis purposes. The question that we have is simply can we access the data stored in the OLAP cube in the same way that we would a View. We utilise a simple VB reporting tool that allows us to dynamically interrogate views in our SQL databases. Would this functionality apply also to OLAP.

Regards

RepomanYou can build a cube off of a single table or from a view.

HTH|||I didn't try it yet, but I know that SQL Server has a different OLE DB provider for it's analytical services. You will need to use it to connect to your cube.|||After further research it appears that we cannot just plug directly in. I think we need to use MDX to call the Cube. The resultant data can then be passed to our report builder.

Many thanks for the answers nonetheless.

Regards

Repoman|||Guess I'm not clear on what you are asking. If you build your cubes with ROLAP (relational - OLAP) the cube will build/store your cube in a relational matter. MDX you can use to build calculated members on a cube, what is it you are trying to do?