default db permissions for account

Hi
I want to allow the windows iusr_computername account exec permissions on
user stored procs and select on views. I have 50 odd stored procs. Is there
a way of assigning permissions so that this account always has those
permissions and the permissions are automatically added when a new view or
sp is added?
I'd also like to easily transfer this to other databases. I'm sure the
answer lies in using roles or scripts or perhaps there's a fundamentally
easy way that I haven't found yet?
Thanks
AndrewHi Andrew,
There is no fundamentally easy way to assign permissions on all the stored
procedures to a user.
You can use the following script to give a user permission on all existing
stored procedures, but you have to re-run it to give permissions to newly
created stored procedures:
DECLARE @.proc_name SYSNAME
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
END
You can use something similar to assign permissions on views using
inforamtion_schema.views.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> Hi
> I want to allow the windows iusr_computername account exec permissions on
> user stored procs and select on views. I have 50 odd stored procs. Is
there
> a way of assigning permissions so that this account always has those
> permissions and the permissions are automatically added when a new view or
> sp is added?
> I'd also like to easily transfer this to other databases. I'm sure the
> answer lies in using roles or scripts or perhaps there's a fundamentally
> easy way that I haven't found yet?
> Thanks
> Andrew
>|||Hi thanks for that.
Just one little problem. I'm getting an error "Invalid parameter 2 specified
for object_id.". I'm afraid my attempts to debug have failed. Can you help?
Thanks again
Andrew
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:Oh%23e9saXDHA.1280@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> There is no fundamentally easy way to assign permissions on all the stored
> procedures to a user.
> You can use the following script to give a user permission on all existing
> stored procedures, but you have to re-run it to give permissions to newly
> created stored procedures:
> DECLARE @.proc_name SYSNAME
> SET @.proc_name = ''
> WHILE 1=1
> BEGIN
> SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
> user stored procedures
> AND ROUTINE_TYPE = 'Procedure'
> AND ROUTINE_NAME > @.proc_name
> ORDER BY ROUTINE_NAME
> )
> IF @.proc_name IS NULL BREAK
> EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
> END
> You can use something similar to assign permissions on views using
> inforamtion_schema.views.
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
> message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > I want to allow the windows iusr_computername account exec permissions
on
> > user stored procs and select on views. I have 50 odd stored procs. Is
> there
> > a way of assigning permissions so that this account always has those
> > permissions and the permissions are automatically added when a new view
or
> > sp is added?
> >
> > I'd also like to easily transfer this to other databases. I'm sure the
> > answer lies in using roles or scripts or perhaps there's a fundamentally
> > easy way that I haven't found yet?
> >
> > Thanks
> > Andrew
> >
> >
>

Decrypting Encrypted Views/Sp/Functions.....?

Hi all,
As all of you are aware you can Encrypt your Triggers/Stored Procedures/Views And Functions
in Sql Server with "WITH ENCRYPTION" clause.recently i came across a Stored procedure on the Net that could reverse and decrypt all Encrypted objects.i personally tested it and it really works.That's fine (of course for some body)
Now i want to know is it a Known Bug for Sql Server 2000 and is there a permanent solution for Encrypting mentioned objects.

Thanks in advance.
Best Regards.

Yes. No.|||

There are huge enhancements with Encrption/Decrption in SQL2005, you may take a look at this article:

http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were managed
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoint
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though the
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/common/survey.aspx?scid=sw;en;1296&showpage=1&ws=msdn&sd=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL
>> Server
>> 2005. I setup a stored procedure to insert to the view, then (in Visual
>> Studio) put a stop in the stored procedure. This works fine, until I try
>> to
>> step into the trigger. Then it tells me that no code is available.
>> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it
>> won't
>> allow me, saying "A breakpoint could not be inserted at this location".
>> This only happens for any trigger on the view. If I try to set a
>> breakpoint
>> on a trigger on a table, it works fine.
>> Does anyone know a way around this? I really would like to step though
>> the
>> trigger on the view.

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>|||Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>