Looking For Technique To Default Execute Permission on any new SPr

Hi,
I'm looking for a way, for when any procedure is created or updated in a
user database, that a particular DB role be granted execute permissions on
the proc.
This role is a: datareader,datawriter, and ddladmin, but can not be
db_owner. I know that I can grant execute en-masse using a variety of T-SQL
techniques, but I would have to do this periodically. Also, adding a GRANT
on the tail-end of the CREATE PROCEDURE would not work for me.
I was beginning to create a trigger on sysobjects for CREATE,UPDATE when the
object type is 'P' (for procedure) to then issue the GRANT, but soon realize
d
that triggers on system tables are not permitted!
Does anyone have any suggestions?
Thanks very much,
SteveTIf you are on SQL Server 2005, you could grant execute
permissions on the schema to the role -
GRANT EXECUTE ON SCHEMA::YourSchema TO YourRole
Otherwise, not much you can do if you can't add a grant
statement to the create procedure script.
-Sue
On Thu, 2 Nov 2006 13:49:02 -0800, SteveT
<SteveT@.discussions.microsoft.com> wrote:

>Hi,
>I'm looking for a way, for when any procedure is created or updated in a
>user database, that a particular DB role be granted execute permissions on
>the proc.
>This role is a: datareader,datawriter, and ddladmin, but can not be
>db_owner. I know that I can grant execute en-masse using a variety of T-SQ
L
>techniques, but I would have to do this periodically. Also, adding a GRANT
>on the tail-end of the CREATE PROCEDURE would not work for me.
>I was beginning to create a trigger on sysobjects for CREATE,UPDATE when th
e
>object type is 'P' (for procedure) to then issue the GRANT, but soon realiz
ed
>that triggers on system tables are not permitted!
>Does anyone have any suggestions?

Looking for Login attempts

Hi,
I want to be able to check who is attempting to login to
my database using
1. NT authentication
2. SQL authentication
Does this happen by default? Is there a log somewhere that
I can go to to see 'Bill tried to login on 10/9/03 but
gave the wrong password'?
CheersHi,
No it wont happen by default.
How to enable audit
Go to enterprise manager and select the server you need to audit then,
1. Right click above the server and select properties
2. Select security tab and select Audit level as ALL.
Thanks
Hari
MCDBA
"Dave Woodward" <anonymous@.discussions.microsoft.com> wrote in message
news:53be01c3d9ca$aba34110$7d02280a@.phx.gbl...

quote:

---

> Hi,
> I want to be able to check who is attempting to login to
> my database using
> 1. NT authentication
> 2. SQL authentication
> Does this happen by default? Is there a log somewhere that
> I can go to to see 'Bill tried to login on 10/9/03 but
> gave the wrong password'?
> Cheers

---

|||This method will tell you whether a login failed or succeed but will not
necessarily tell you why it failed.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||>--Original Message--

quote:

---

>This method will tell you whether a login failed or

---

succeed but will not

quote:

---

>necessarily tell you why it failed.

---

Indeed. It would also be nice if it included where the
connection came from(workstation, ip address etc) More
information is better in this case.|||>--Original Message--

quote:

---

>This method will tell you whether a login failed or

---

succeed but will not

quote:

---

>necessarily tell you why it failed.

---

Indeed. It would also be nice if it included where the
connection came from(workstation, ip address etc) More
information is better in this case.|||Where does the data appear?
How do you access it?

quote:

---

>--Original Message--
>succeed but will not
>Indeed. It would also be nice if it included where the
>connection came from(workstation, ip address etc) More
>information is better in this case.
>.
>

---

|||Hi,
Easy way is from query analyzer execute,
xp_readerrorlog
otherwise go the Enterprise manager ,
managerment, Errorlogs.
Thanks
Hari
MCDBA
"Dave Woodward" <anonymous@.discussions.microsoft.com> wrote in message
news:06b201c3dab8$29380280$a001280a@.phx.gbl...[QUOTE]
> Where does the data appear?
> How do you access it?