How can I get the list of reports that a user has only view reports access ?
the user is in windows server 2003 active directory.
reporting services 2000 is being used.
within the intranet there is a portal.
in the portal I want to list the available reports to the user
that he can access. I do not want to display all the reports to him and let
him choose. I want him to be able to see only the reports he is allowed to
see?
how can I do this using asp.net 1.1
thanx
rifatis it possible or not?
can someone answer please ?
thanx
rifatsql
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Friday, March 23, 2012
Listing tables in a database
Rich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> RichHello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
>|||Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
Rich|||Rich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
>|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> RichHello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
>|||Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
Rich|||Rich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
>|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'
Wednesday, March 21, 2012
listing columns in view and stored procedures
Using SS2000 SP4. If I create a view and list the columns in the select
statement, if I then create a sp using the view is it better to list the
columns again or just use '*'?
Thanks,
--
Dan D.Do not use *. You will not know what you are selecting if your view
definition is changed.
"Dan D." wrote:
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||You will get varying opinions here, especially since we don't know what your
definition of "better" is.
My opinion:
*ALWAYS* list your columns in production code, and never use SELECT *.
Too many things can go wrong throughout the pipeline.
A
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Good point. Thanks.
--
Dan D.
"Omnibuzz" wrote:
> Do not use *. You will not know what you are selecting if your view
> definition is changed.
> --
>
>
> "Dan D." wrote:
>|||That's how I feel. I saw a piece of code that was written using '*' and
wondered what other people thought.
Thanks,
--
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:
> You will get varying opinions here, especially since we don't know what yo
ur
> definition of "better" is.
> My opinion:
> *ALWAYS* list your columns in production code, and never use SELECT *.
> Too many things can go wrong throughout the pipeline.
> A
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||The person that wrote this should be shackled and whipped.
Since this is probably illegal in most states, provinces and countries...
He should at least be forced to write 100 times on a blackboard:
I shall never use "Select *" in production code again.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
> That's how I feel. I saw a piece of code that was written using '*' and
> wondered what other people thought.
> Thanks,
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||I'll see if I can track him/her down.:)
--
Dan D.
"Raymond D'Anjou" wrote:
> The person that wrote this should be shackled and whipped.
> Since this is probably illegal in most states, provinces and countries...
> He should at least be forced to write 100 times on a blackboard:
> I shall never use "Select *" in production code again.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
>
>|||To add to the topic.
We now have a new company standard.. that you list ALL FIELDS in your INSERT
statements.
For Example:
at one point , an Emp table has EmpID, LastName, FirstName columns.
We had code like this
INSERT INTO Emp Values (101, 'Smith', 'John')
...
Why is this bad'
Someone adds a new column
Emp.Age.
Now every INSERT fails. Because the table has 4 columns, and the INSERT
supplies 3.
..
I can't tell you how many bugs I've tracked down with that (stupid) issue.
ALWAYS use a list. If it wasn't for quick debugging , Select * should be
outlawed! (Maybe a little extreme, but it can cause alot of issues)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Thanks for your 2cents Sloan.
--
Dan D.
"sloan" wrote:
> To add to the topic.
> We now have a new company standard.. that you list ALL FIELDS in your INSE
RT
> statements.
> For Example:
> at one point , an Emp table has EmpID, LastName, FirstName columns.
> We had code like this
> INSERT INTO Emp Values (101, 'Smith', 'John')
> ...
> Why is this bad'
> Someone adds a new column
> Emp.Age.
> Now every INSERT fails. Because the table has 4 columns, and the INSERT
> supplies 3.
> ...
> I can't tell you how many bugs I've tracked down with that (stupid) issue.
> ALWAYS use a list. If it wasn't for quick debugging , Select * should be
> outlawed! (Maybe a little extreme, but it can cause alot of issues)
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||On Mon, 1 May 2006 10:23:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. If I create a view and list the columns in the select
>statement, if I then create a sp using the view is it better to list the
>columns again or just use '*'?
>Thanks,
Aaron promised you varying opinions, but everyone was taking the same
side, so I figure it was time to add my two cents.
I consider * to be a very valuable tool in the SELECT list, and
prefer it in many situations. In general I prefer * when the query
MUST include EVERY column from a table. It avoids the possible error
of leaving a column out, and it enforces a uniform sequence to the
columns that can't hurt.
Example: A view that has to include every column from a table, plus
other columns:
SELECT X.*, Y.SomeCol
If table X changes, all the is needed is to ALTER the view (with no
changes) to force a recompile.
When there are two tables with identical layouts and rows are inserted
from one into the other:
INSERT X
SELECT * FROM Y
It is very hard to get that wrong. Again, if the tables change all
that is required is a recompile, removing one more chance to make an
error. If only one of the tables change the recompile will fail,
which is a Good Thing as the issue of how to deal with the change was
not addressed, and needs to be. I prefer such a failure to having the
difference remain unadressed.
Roy Harvey
Beacon Falls, CT
statement, if I then create a sp using the view is it better to list the
columns again or just use '*'?
Thanks,
--
Dan D.Do not use *. You will not know what you are selecting if your view
definition is changed.
"Dan D." wrote:
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||You will get varying opinions here, especially since we don't know what your
definition of "better" is.
My opinion:
*ALWAYS* list your columns in production code, and never use SELECT *.
Too many things can go wrong throughout the pipeline.
A
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Good point. Thanks.
--
Dan D.
"Omnibuzz" wrote:
> Do not use *. You will not know what you are selecting if your view
> definition is changed.
> --
>
>
> "Dan D." wrote:
>|||That's how I feel. I saw a piece of code that was written using '*' and
wondered what other people thought.
Thanks,
--
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:
> You will get varying opinions here, especially since we don't know what yo
ur
> definition of "better" is.
> My opinion:
> *ALWAYS* list your columns in production code, and never use SELECT *.
> Too many things can go wrong throughout the pipeline.
> A
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||The person that wrote this should be shackled and whipped.
Since this is probably illegal in most states, provinces and countries...
He should at least be forced to write 100 times on a blackboard:
I shall never use "Select *" in production code again.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
> That's how I feel. I saw a piece of code that was written using '*' and
> wondered what other people thought.
> Thanks,
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||I'll see if I can track him/her down.:)
--
Dan D.
"Raymond D'Anjou" wrote:
> The person that wrote this should be shackled and whipped.
> Since this is probably illegal in most states, provinces and countries...
> He should at least be forced to write 100 times on a blackboard:
> I shall never use "Select *" in production code again.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
>
>|||To add to the topic.
We now have a new company standard.. that you list ALL FIELDS in your INSERT
statements.
For Example:
at one point , an Emp table has EmpID, LastName, FirstName columns.
We had code like this
INSERT INTO Emp Values (101, 'Smith', 'John')
...
Why is this bad'
Someone adds a new column
Emp.Age.
Now every INSERT fails. Because the table has 4 columns, and the INSERT
supplies 3.
..
I can't tell you how many bugs I've tracked down with that (stupid) issue.
ALWAYS use a list. If it wasn't for quick debugging , Select * should be
outlawed! (Maybe a little extreme, but it can cause alot of issues)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Thanks for your 2cents Sloan.
--
Dan D.
"sloan" wrote:
> To add to the topic.
> We now have a new company standard.. that you list ALL FIELDS in your INSE
RT
> statements.
> For Example:
> at one point , an Emp table has EmpID, LastName, FirstName columns.
> We had code like this
> INSERT INTO Emp Values (101, 'Smith', 'John')
> ...
> Why is this bad'
> Someone adds a new column
> Emp.Age.
> Now every INSERT fails. Because the table has 4 columns, and the INSERT
> supplies 3.
> ...
> I can't tell you how many bugs I've tracked down with that (stupid) issue.
> ALWAYS use a list. If it wasn't for quick debugging , Select * should be
> outlawed! (Maybe a little extreme, but it can cause alot of issues)
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||On Mon, 1 May 2006 10:23:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. If I create a view and list the columns in the select
>statement, if I then create a sp using the view is it better to list the
>columns again or just use '*'?
>Thanks,
Aaron promised you varying opinions, but everyone was taking the same
side, so I figure it was time to add my two cents.
I consider * to be a very valuable tool in the SELECT list, and
prefer it in many situations. In general I prefer * when the query
MUST include EVERY column from a table. It avoids the possible error
of leaving a column out, and it enforces a uniform sequence to the
columns that can't hurt.
Example: A view that has to include every column from a table, plus
other columns:
SELECT X.*, Y.SomeCol
If table X changes, all the is needed is to ALTER the view (with no
changes) to force a recompile.
When there are two tables with identical layouts and rows are inserted
from one into the other:
INSERT X
SELECT * FROM Y
It is very hard to get that wrong. Again, if the tables change all
that is required is a recompile, removing one more chance to make an
error. If only one of the tables change the recompile will fail,
which is a Good Thing as the issue of how to deal with the change was
not addressed, and needs to be. I prefer such a failure to having the
difference remain unadressed.
Roy Harvey
Beacon Falls, CT
Listing all Indexes
Still using SQL7.
I am wondering how come there is not an Information_Schema view that
lists indexes? Information_Schema is supposed to be the safest way to
obtain information on metadata, but it appears that the only way to
get a list of indexes is with a system stored proc.Zack Sessions (zcsessions@.visionair.com) writes:
> Still using SQL7.
> I am wondering how come there is not an Information_Schema view that
> lists indexes? Information_Schema is supposed to be the safest way to
> obtain information on metadata, but it appears that the only way to
> get a list of indexes is with a system stored proc.
I guess this is because INFORMATION_SCHEMA comes from ANSI, and ANSI
does not know what an index is, as index is regarded to be an implementation
issue. And someone leaped to the conclusion that INFORMATION_SCHEMA
could replace system tables.
One of the reasons I prefer the system tables (together with property
functions) is that they represent complete information set.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Monday, March 19, 2012
List Tables in a view?
Is there a way to easily list the tables/views that a view is using to get its data?
Thanks in advance,
Shawnsp_depends <Viewname>
Books online {sp_depends}
Thanks in advance,
Shawnsp_depends <Viewname>
Books online {sp_depends}
Monday, March 12, 2012
List only the visible columns in a view?
In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get a
row for every column in a view including those that might not be visible but
used only in the WHERE or ORDER BY clauses for example.
Is there an easy way to reduce the list to only show the columns that
actually appear in the SELECT clause of the view definition?
I've tried examining Profiler when expanding the columns node under the View
in Management Studio as it seems to load only one node per visible column
but haven't been able to make much sense of how it does this.
BTW I'm using that view as it promises to only show views and columns "to
which the current user has permissions". Is this reliable or is there a
preferred way of listing visible views, talbe, & columns etc?
cheers,
Paul.Hi, Paul
Look in the INFORMATION_SCHEMA.COLUMNS view.
Razvan|||Use the catalog schema views...
SELECT
name
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.YourViewName');
"Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get
> a
> row for every column in a view including those that might not be visible
> but
> used only in the WHERE or ORDER BY clauses for example.
> Is there an easy way to reduce the list to only show the columns that
> actually appear in the SELECT clause of the view definition?
> I've tried examining Profiler when expanding the columns node under the
> View
> in Management Studio as it seems to load only one node per visible column
> but haven't been able to make much sense of how it does this.
> BTW I'm using that view as it promises to only show views and columns "to
> which the current user has permissions". Is this reliable or is there a
> preferred way of listing visible views, talbe, & columns etc?
> cheers,
> Paul.
>|||Thanks Aaron - much appreciated.
Is there an easy way to determine which columns a user might have access to
when using the "sys" tables? That was what the INFORMATION_SCHEMA views
seemed to promise although there was some doubt in newsgroups as to their
accuracy in SQL2000 due to them being derived from the sysdepends table.
I'm betting that this will have changed in 2005.
However moving back to the sys tables/views will mean I have to determine
this permission information in some other way. Any seggestions would be
appreciated tremendously.
cheers,
Paul.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCq14hWBGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Use the catalog schema views...
> SELECT
> name
> FROM
> sys.columns
> WHERE
> object_id = OBJECT_ID('dbo.YourViewName');
>
> "Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
> message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
>|||> Is there an easy way to determine which columns a user might have access
> to when using the "sys" tables?
Take a look at sys.database_permissions
I believe the join between sys.database_permissions p and sys.comments c
would be:
ON
c.object_id = p.major_id
AND c.column_id = p.minor_id
WHERE
p.class = 1
AND p.grantee_principal_id = SUSER_ID('username');
I am not 100% sure on that, and don't have time today to experiment with
column-level permissions. But hopefully that gets you started.
> accuracy in SQL2000 due to them being derived from the sysdepends table.
Permissions derived from sysdepends? I think that is inaccurate.
A
row for every column in a view including those that might not be visible but
used only in the WHERE or ORDER BY clauses for example.
Is there an easy way to reduce the list to only show the columns that
actually appear in the SELECT clause of the view definition?
I've tried examining Profiler when expanding the columns node under the View
in Management Studio as it seems to load only one node per visible column
but haven't been able to make much sense of how it does this.
BTW I'm using that view as it promises to only show views and columns "to
which the current user has permissions". Is this reliable or is there a
preferred way of listing visible views, talbe, & columns etc?
cheers,
Paul.Hi, Paul
Look in the INFORMATION_SCHEMA.COLUMNS view.
Razvan|||Use the catalog schema views...
SELECT
name
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.YourViewName');
"Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get
> a
> row for every column in a view including those that might not be visible
> but
> used only in the WHERE or ORDER BY clauses for example.
> Is there an easy way to reduce the list to only show the columns that
> actually appear in the SELECT clause of the view definition?
> I've tried examining Profiler when expanding the columns node under the
> View
> in Management Studio as it seems to load only one node per visible column
> but haven't been able to make much sense of how it does this.
> BTW I'm using that view as it promises to only show views and columns "to
> which the current user has permissions". Is this reliable or is there a
> preferred way of listing visible views, talbe, & columns etc?
> cheers,
> Paul.
>|||Thanks Aaron - much appreciated.
Is there an easy way to determine which columns a user might have access to
when using the "sys" tables? That was what the INFORMATION_SCHEMA views
seemed to promise although there was some doubt in newsgroups as to their
accuracy in SQL2000 due to them being derived from the sysdepends table.
I'm betting that this will have changed in 2005.
However moving back to the sys tables/views will mean I have to determine
this permission information in some other way. Any seggestions would be
appreciated tremendously.
cheers,
Paul.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCq14hWBGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Use the catalog schema views...
> SELECT
> name
> FROM
> sys.columns
> WHERE
> object_id = OBJECT_ID('dbo.YourViewName');
>
> "Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
> message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
>|||> Is there an easy way to determine which columns a user might have access
> to when using the "sys" tables?
Take a look at sys.database_permissions
I believe the join between sys.database_permissions p and sys.comments c
would be:
ON
c.object_id = p.major_id
AND c.column_id = p.minor_id
WHERE
p.class = 1
AND p.grantee_principal_id = SUSER_ID('username');
I am not 100% sure on that, and don't have time today to experiment with
column-level permissions. But hopefully that gets you started.
> accuracy in SQL2000 due to them being derived from the sysdepends table.
Permissions derived from sysdepends? I think that is inaccurate.
A
List of users?
How can i get a list of user that have access to all my databases?
i know how to view them but i need to output them to a text file or excel.right click on Logins then select Export List...|||Hi
Try this code
use somedatabase
go
select * from sysusers
i know how to view them but i need to output them to a text file or excel.right click on Logins then select Export List...|||Hi
Try this code
use somedatabase
go
select * from sysusers
List of users + roles + rights
How can I generate a list/view of all users/groups with appropriate roles and rigths?SP_HELPROTECT list all the information in the database.|||Hi,
Can someone please let me know if there is some way to find out the roles of a user in SQL server 2000?
The sysusers table has a column called roles that stores a bit set for every role associated with the user. But given a user name / uid how to find out all the roles (role ids will do) associated with that user?
Please help ... its urgent.
Thanks for your time,
Sayali|||In SQL-2000, this is stored in sysmembers (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-m_6f3n.asp).
-PatP
Can someone please let me know if there is some way to find out the roles of a user in SQL server 2000?
The sysusers table has a column called roles that stores a bit set for every role associated with the user. But given a user name / uid how to find out all the roles (role ids will do) associated with that user?
Please help ... its urgent.
Thanks for your time,
Sayali|||In SQL-2000, this is stored in sysmembers (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-m_6f3n.asp).
-PatP
List of tables in use by a view
Hi,
I wonder if I can list the tables (and views) used inside a view, I mean the list of tables in the FROM clause
Thanks,
Arty
An easy way is to call the "sp_depends @.objname" stored procedure. Within Management Studio, you can also right-click on an object and choose "View Dependencies"
Peter
|||I would like to do it inside VB.NET or C# using the SMO|||One way is to use SMO like:Database db = srv.Databases["test"];
string a = "sp_depends " + objname;
db.ExecuteWithResults(a);
There are other ways of using SMO to get the dependency information, but I do not have the code available.
Peter
Wednesday, March 7, 2012
List of data mining techniques not populated - BI Studio hangs
From within the Business Intelligence Studio, I've created a data source and a data source view. Next, when I try to build a mining structure from a relational database, the studio hangs when it goes to the screen which lists the data mining techniques.
Has anybody come across this issue? What needs to be done to get further?
I'm using the June CTP of SQL Server 2005.
Thanks,
-GB.
In my case, there was no previous installation of Analysis Services. However, there was an installation of SQL Server 2005 Express Edition, which the install program for SQL Server 2005 CTP detected, and asked me to remove (including a Beta of .NET version 2.0).
-GB.|||
A few questions:
Can you connect to your AS instance from SQL Mgmt Studio or another client?
Are you running a named instance? If yes, make sure that the SQL Server Browser service is functioning correctly.
Do you have network connectivity? There is a known issue with client-server connectivity when you're off the network.
|||Thank you for your reply, Raman.I can connect to the AS instance from within SQL Management Studio. I can see the data source and data source views that I've already saved before starting with the data mining structure creation. I do not use any other client to connect to this instance.
I am using a default non-named instance.
About network connectivity: I have the TCP / IP stack running, but it is not (always) connected to a network. I also have the ZoneAlaram personal software firewall running. I've turned this off, but it still does not work.
Thanks,
-GB.|||This might be the result of a configuration/install bug. I would suggest uninstalling your June CTP version and moving to the RTM build.
List of data mining techniques not populated - BI Studio hangs
From within the Business Intelligence Studio, I've created a data source and a data source view. Next, when I try to build a mining structure from a relational database, the studio hangs when it goes to the screen which lists the data mining techniques.
Has anybody come across this issue? What needs to be done to get further?
I'm using the June CTP of SQL Server 2005.
Thanks,
-GB.
In my case, there was no previous installation of Analysis Services. However, there was an installation of SQL Server 2005 Express Edition, which the install program for SQL Server 2005 CTP detected, and asked me to remove (including a Beta of .NET version 2.0).
-GB.|||
A few questions:
Can you connect to your AS instance from SQL Mgmt Studio or another client?
Are you running a named instance? If yes, make sure that the SQL Server Browser service is functioning correctly.
Do you have network connectivity? There is a known issue with client-server connectivity when you're off the network.
|||Thank you for your reply, Raman.I can connect to the AS instance from within SQL Management Studio. I can see the data source and data source views that I've already saved before starting with the data mining structure creation. I do not use any other client to connect to this instance.
I am using a default non-named instance.
About network connectivity: I have the TCP / IP stack running, but it is not (always) connected to a network. I also have the ZoneAlaram personal software firewall running. I've turned this off, but it still does not work.
Thanks,
-GB.|||This might be the result of a configuration/install bug. I would suggest uninstalling your June CTP version and moving to the RTM build.
List of all database users
We had 40+ databases and like to see all of users in these databases. What
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:
> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
>
>
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:
> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
>
>
List of all database users
We had 40+ databases and like to see all of users in these databases. What
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:
> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> > We had 40+ databases and like to see all of users in these databases.
> > What
> > is the data dictionary, the system table, or the sys view can list all
> > database users?
>
>
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:
> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> > We had 40+ databases and like to see all of users in these databases.
> > What
> > is the data dictionary, the system table, or the sys view can list all
> > database users?
>
>
Friday, February 24, 2012
List database users & login
In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peter
Hello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peter
Hello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
List database users & login
In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
List database users & login
In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
Subscribe to:
Posts (Atom)