Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retrieve list of tables without triggers

I need to do some db cleaning, how can I get a list of tables within the
database that do not have any triggers.
Thanks in advanceHi scuba79,
Try the following on your database:
select name from sysobjects
where xtype = 'U'
and id not in
(
select parent_obj from sysobjects
where xtype = 'TR'
)
xtype = 'U' will return all the user tables within your database
xtype = 'TR' are trigger objects.
Every trigger belongs to a table/parent and the parent object id is store in
sysobjects.parent_obj. By using this info we can filter out all the user
tables that have triggers.
Hope this help.
"scuba79" wrote:

> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance|||Here is one way
SELECT * FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
FROM
sysobjects o
WHERE
OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
http://sqlservercode.blogspot.com/|||Little correction I forgot to filter out the views
SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
FROM
sysobjects o
WHERE
OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
http://sqlservercode.blogspot.com/|||assuming sql2000:
select user_name(uid) as table_schema, name as table_name
from sysobjects so
where xtype='U'
and objectproperty(id,'IsMSShipped')=0
and not exists (
select *
from sysobjects
where xtype='TR'
and parent_obj = so.id
)
order by table_name
scuba79 wrote:
> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance