How to show roles with permissions to objects

Hello,

I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!

BruceHave you checked "sp_helprotect" , this one is permissions for all objects,

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

"Bruce" <deluxeinformation@.gmail.comwrote in message
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hello,
>
I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!
>
Bruce
>

|||On Mar 14, 4:41 am, "Jack Vamvas" <DEL_TO_RE...@.del.comwrote:

Quote:

Originally Posted by

Have you checked "sp_helprotect" , this one is permissions for all objects,
>
--
>
Jack Vamvas
___________________________________
The latest IT jobs -www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
>
"Bruce" <deluxeinformat...@.gmail.comwrote in message
>
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!

>

Quote:

Originally Posted by

Bruce

Thank you. I don't know how I overlooked that one. Sometimes I wish
BOL was laid out a little differently.

Bruce

How to show a line between table column headers and the details

I am using table object to present report.

table row1 has all the column names and table row2 has the data.

How can i put a horizontal line between column names and the data rows.

Thank you very much.

Would adding a solid border to the bottom of the row having the column headers in it work?

Jarret

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--