mgmt studio-View user permissions?

Using SQL server 2000 Enterprise Manager, one can bring up the properties of
a database user, click the Permissions button, and see all the permissions
that have been set for Stored Procs, tables etc... One can modify these as
needed.
How do I use Management Studio to do this?
I tried selecting the user (under security for that database) and was
expecting to see it under the Securables page under the Explicit permissions
- but I don't see it there - the user has been granted execute permissions to
some of the stored procedures and I can verify that by selecting the stored
proc and selecting the Permissions page under it's properties...
I am running SQL server 2005 Standard on a WinXP SP2.
Hi,
In user properties window and securables, try adding all stored procedures
with Add button.
Then you select all objects of type and select stored procedures.
Then just click on stored procedure and permissions should be written in
bottom of the window.
Danijel Novak
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:43F72B0A-113D-4A7E-A97D-0D63C77EF41B@.microsoft.com...
> Using SQL server 2000 Enterprise Manager, one can bring up the properties
> of
> a database user, click the Permissions button, and see all the permissions
> that have been set for Stored Procs, tables etc... One can modify these
> as
> needed.
> How do I use Management Studio to do this?
> I tried selecting the user (under security for that database) and was
> expecting to see it under the Securables page under the Explicit
> permissions
> - but I don't see it there - the user has been granted execute permissions
> to
> some of the stored procedures and I can verify that by selecting the
> stored
> proc and selecting the Permissions page under it's properties...
> I am running SQL server 2005 Standard on a WinXP SP2.
|||Thanks for the quick response Danijel. I guess I was looking for something
similar to what Enterprise manager offered "List only objects with
permissions for this user" option when we select the permissions for a user
in the database context.
Is there a way to see these?
Thanks again!
"Danijel Novak" wrote:

> Hi,
> In user properties window and securables, try adding all stored procedures
> with Add button.
> Then you select all objects of type and select stored procedures.
> Then just click on stored procedure and permissions should be written in
> bottom of the window.
> --
> Danijel Novak
>
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:43F72B0A-113D-4A7E-A97D-0D63C77EF41B@.microsoft.com...
>
>
|||Hi,
For that purpose I'm using following T-SQL...
SELECT OBJECT_NAME(major_id), permission_name, state_desc FROM
sys.database_permissions WHERE grantee_principal_id = USER_ID('db_user')
Hope this is what you're looking for...
Danijel Novak
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:8B465B77-A3D1-4B4C-80ED-A88DCD4B93D6@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response Danijel. I guess I was looking for something
> similar to what Enterprise manager offered "List only objects with
> permissions for this user" option when we select the permissions for a
> user
> in the database context.
> Is there a way to see these?
> Thanks again!
> "Danijel Novak" wrote:

Metadata in sql server 2005

Hi
I add extended properties to table in sql server 2005, for example i add Caption property to every column.
In case I generate a datasource from table in sql server in visual studio 2005, Is there a way that "Caption" property in datatable to be filled from that metadata, or metadata is useless for dataset.
Every time i had to fill manually the caption property of datatable generated .

hi,

you can access those extended properties calling fn_listextendedproperty function, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.Invoice ( ID INT NOT NULL PRIMARY KEY , BillNo INT NOT NULL -- , others ) GO EXEC sp_addextendedproperty '1st property' , '1St property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; EXEC sp_addextendedproperty '2nd property' , '2nd property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; GO PRINT 'All available properties'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , CONVERT(VARCHAR(20), [value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( NULL , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO PRINT 'just the required [1st property] property'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , convert(VARCHAR(20),[value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( '1st property' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO DROP TABLE dbo.Invoice; --< All available properties Property Name Property vCharValue -- -- 1st property 1St property value 2nd property 2nd property value just the required [1st property] property Property Name Property vCharValue -- -- 1st property 1St property value

so, assuming you are interested in the Invoice management, you have to query for all the properties of the dbo.Invoice table columns...

this can obviously fill a dataset or a datareader you can then consume to map as desired the returned values so you could assume the "1st property" is the caption of the corresponding UI control and you can set it accordingly..

BTW, to manage those properties outside the Microsoft tools you have to resort on http://msdn2.microsoft.com/en-us/library/aa174648(SQL.80).aspx and http://msdn2.microsoft.com/en-us/library/ms190243.aspx

regards|||Thank you for your reply.
I mean , what is the extended properties that may be used automatically by visual studio 2005.
For example , Can the "Caption " property of datatable created in vs 2005 , be set automatically using extended properties?
This property is useful, because form wizard creates label with that "caption" value(when i set it at design time), and textbox bound to column.

|||

hi,

no, you have to deal with that "by hand"...

regards

Metadata Detail

Hi all
I want to get some metadata information for my tables
Some column properties I can get using System tabes and Information Schema
but I can not get some information as below
I want to get information of column properties Identity Seed, Identity
Increment, Is Rowguid , Formula value, Description for my any column which
this property applicable.
Any help will be highly appreciated
Thanks> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT. For
"Description" use system function fn_listextendedproperty and for "Formula
value" check system table syscomments.
Example:
use northwind
go
create table dbo.t (
colA int not null identity,
colB uniqueidentifier ROWGUIDCOL not null default(newid()),
colC as power(2, colA),
colD as colA % 10
)
go
select
ordinal_position,
column_name,
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else 'No'
end as [IsIdentity],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_seed(table_name)) else '' end as [ident_seed],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_incr(table_name)) else '' end as [ident_incr],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_current(table_name)) else '' end as [ident_current],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else 'No
'
end as [IsRowGuidCol],
coalesce(sc.[text], '') as [Formula]
from
information_schema.columns as c
left join
syscomments as sc
on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
sc.[id] and sc.number = c.ordinal_position
where
table_name = 't'
order by
ordinal_position
go
drop table t
go
AMB
"AM" wrote:

> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information Schem
a
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>
>|||Thanks
It helps me a lot
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
which
> Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT.
For
> "Description" use system function fn_listextendedproperty and for "Formula
> value" check system table syscomments.
> Example:
> use northwind
> go
> create table dbo.t (
> colA int not null identity,
> colB uniqueidentifier ROWGUIDCOL not null default(newid()),
> colC as power(2, colA),
> colD as colA % 10
> )
> go
> select
> ordinal_position,
> column_name,
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else
'No'
> end as [IsIdentity],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_seed(table_name)) else '' end as [ident_seed],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_incr(table_name)) else '' end as [ident_incr],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_current(table_name)) else '' end as [ident_current],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else
'No'
> end as [IsRowGuidCol],
> coalesce(sc.[text], '') as [Formula]
> from
> information_schema.columns as c
> left join
> syscomments as sc
> on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
> sc.[id] and sc.number = c.ordinal_position
> where
> table_name = 't'
> order by
> ordinal_position
> go
> drop table t
> go
>
> AMB
>
> "AM" wrote:
>
Schema
which|||See if this helps:
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"AM" wrote:

> Thanks
> It helps me a lot
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
> which
> For
> 'No'
> 'No'
> Schema
> which
>
>|||Also check out following link for " Schema: How do I show the description
property of a column? ":
http://www.aspfaq.com/show.asp?id=2244
"AM" <anonymous@.examnotes.net> wrote in message
news:%23WseefgOFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information
> Schema
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column
> which
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>