How can I do
create proc MyProc
as
--...proc logic
go
grant execute on MyProc to MYCOMPUTER\ASPNET
I can do the 'grant' statement where the user name doesn't include a
computer prefix - but the ASPNET account does! It keeps complaining, citing
'Incorrect syntax near \'.
The following doesn't work either.
grant execute on MyProc to 'MYCOMPUTER\ASPNET'
Any suggestions?Bonj,
I think MYCOMPUTER\ASPNET is the login name, What is the user name
associated to this login in your db?
AMB
"Bonj" wrote:
> How can I do
> create proc MyProc
> as
> --...proc logic
> go
> grant execute on MyProc to MYCOMPUTER\ASPNET
> I can do the 'grant' statement where the user name doesn't include a
> computer prefix - but the ASPNET account does! It keeps complaining, citin
g
> 'Incorrect syntax near '.
> The following doesn't work either.
> grant execute on MyProc to 'MYCOMPUTER\ASPNET'
>
> Any suggestions?|||assuming MYCOMPUTER\ASPNET is a defined login, then:
grant execute on MyProc to "MYCOMPUTER\\ASPNET"
note double quotes
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:C4F7742C-22E5-4FEB-BE1B-3570FC0645B0@.microsoft.com...
| How can I do
|
| create proc MyProc
| as
| --...proc logic
| go
| grant execute on MyProc to MYCOMPUTER\ASPNET
|
| I can do the 'grant' statement where the user name doesn't include a
| computer prefix - but the ASPNET account does! It keeps complaining,
citing
| 'Incorrect syntax near \'.
| The following doesn't work either.
| grant execute on MyProc to 'MYCOMPUTER\ASPNET'
|
|
| Any suggestions?sql
Showing posts with label proc. Show all posts
Showing posts with label proc. Show all posts
Thursday, March 29, 2012
Granting permission on multiple stored procs
Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?GRANT EXEC ON <proc> TO <MyUser>
just about anything you can do in the EM can be scripted saved as script and used again. which makes you more efficient.|||but the rest of the story is this...
the project is a work in progress thing. we are added new stored procs as needed (up to 83 as of this morning) and now we wanted a selected user to test the new project. I was looking for a way to not have to type out all 83 stored proc names in order to grant access to them for this user. I was hoping for a solution/feature that i was not aware of in EM. so it looks like i will have to do this process manually either way (EM or Script)...|||select 'grant execute on ' + name + ' to user'
from sysobjects
where type = 'P'
and crdate > getdate() -1|||You are using Database Roles, I hope.
just about anything you can do in the EM can be scripted saved as script and used again. which makes you more efficient.|||but the rest of the story is this...
the project is a work in progress thing. we are added new stored procs as needed (up to 83 as of this morning) and now we wanted a selected user to test the new project. I was looking for a way to not have to type out all 83 stored proc names in order to grant access to them for this user. I was hoping for a solution/feature that i was not aware of in EM. so it looks like i will have to do this process manually either way (EM or Script)...|||select 'grant execute on ' + name + ' to user'
from sysobjects
where type = 'P'
and crdate > getdate() -1|||You are using Database Roles, I hope.
Friday, March 23, 2012
Grant Create/Drop user Table permissions?
HI All,
I have a stored proc that runs and creates a temporary table for collecting
data and then drops it when it is done. My problem is that I can, as DBO,
run this fine but my users can not. How do I allow Create/Drop User tables
from a stored proc? Let me phrase that a different way; what kind of
permissions do I need to set up so that user's can run this stored proc that
creates/drops a temporary table? The stored proc already as the required
EXEC permissions for the user/groups to run it.
Thanks very much,
John.Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Excellent Idea! I completely forgot about that.
Thanks very much :)
By the way, is it necessary to drop temporary tables and or check for their
existence before creating them?
Thanks again ,
John.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Thanks for the correct syntax; I was drawing a mind blank this morning. Is
it possible to grant the same prev to a group instead of individual users?
John.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:us9TIRa6DHA.3360@.tk2msftngp13.phx.gbl...
Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi John,
Temporary tables are dropped automatically when they go out of scope, which
means that if you create a temporary table in a stored procedure the
temporary table will be dropped when the stored procedure completes. In any
case, temporary tables are dropped when the user diconnects from the
database, and internally temporary tables created by different users have
different names, although they all seem to have the same name to the users,
so multiple users can create the same temporary table at the same time.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:uOWIdea6DHA.2760@.TK2MSFTNGP09.phx.gbl...
> Excellent Idea! I completely forgot about that.
> Thanks very much :)
> By the way, is it necessary to drop temporary tables and or check for
their
> existence before creating them?
> Thanks again ,
> John.
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
> Hi John,
> Your users don't need any special permissions to create temporary tables,
> that is if you use real temporary tables, the ones prefixed with a #
> character. From your narrative I get the impression that you use permanent
> tables as temporary tables, but that is not advisable.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > HI All,
> >
> > I have a stored proc that runs and creates a temporary table for
> collecting
> > data and then drops it when it is done. My problem is that I can, as
DBO,
> > run this fine but my users can not. How do I allow Create/Drop User
> tables
> > from a stored proc? Let me phrase that a different way; what kind of
> > permissions do I need to set up so that user's can run this stored proc
> that
> > creates/drops a temporary table? The stored proc already as the required
> > EXEC permissions for the user/groups to run it.
> >
> > Thanks very much,
> > John.
> >
> >
>
>
I have a stored proc that runs and creates a temporary table for collecting
data and then drops it when it is done. My problem is that I can, as DBO,
run this fine but my users can not. How do I allow Create/Drop User tables
from a stored proc? Let me phrase that a different way; what kind of
permissions do I need to set up so that user's can run this stored proc that
creates/drops a temporary table? The stored proc already as the required
EXEC permissions for the user/groups to run it.
Thanks very much,
John.Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Excellent Idea! I completely forgot about that.
Thanks very much :)
By the way, is it necessary to drop temporary tables and or check for their
existence before creating them?
Thanks again ,
John.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Thanks for the correct syntax; I was drawing a mind blank this morning. Is
it possible to grant the same prev to a group instead of individual users?
John.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:us9TIRa6DHA.3360@.tk2msftngp13.phx.gbl...
Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi John,
Temporary tables are dropped automatically when they go out of scope, which
means that if you create a temporary table in a stored procedure the
temporary table will be dropped when the stored procedure completes. In any
case, temporary tables are dropped when the user diconnects from the
database, and internally temporary tables created by different users have
different names, although they all seem to have the same name to the users,
so multiple users can create the same temporary table at the same time.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:uOWIdea6DHA.2760@.TK2MSFTNGP09.phx.gbl...
> Excellent Idea! I completely forgot about that.
> Thanks very much :)
> By the way, is it necessary to drop temporary tables and or check for
their
> existence before creating them?
> Thanks again ,
> John.
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
> Hi John,
> Your users don't need any special permissions to create temporary tables,
> that is if you use real temporary tables, the ones prefixed with a #
> character. From your narrative I get the impression that you use permanent
> tables as temporary tables, but that is not advisable.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > HI All,
> >
> > I have a stored proc that runs and creates a temporary table for
> collecting
> > data and then drops it when it is done. My problem is that I can, as
DBO,
> > run this fine but my users can not. How do I allow Create/Drop User
> tables
> > from a stored proc? Let me phrase that a different way; what kind of
> > permissions do I need to set up so that user's can run this stored proc
> that
> > creates/drops a temporary table? The stored proc already as the required
> > EXEC permissions for the user/groups to run it.
> >
> > Thanks very much,
> > John.
> >
> >
>
>
Grant Create/Drop user Table permissions?
HI All,
I have a stored proc that runs and creates a temporary table for collecting
data and then drops it when it is done. My problem is that I can, as DBO,
run this fine but my users can not. How do I allow Create/Drop User tables
from a stored proc? Let me phrase that a different way; what kind of
permissions do I need to set up so that user's can run this stored proc that
creates/drops a temporary table? The stored proc already as the required
EXEC permissions for the user/groups to run it.
Thanks very much,
John.Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
collecting
tables
that
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
collecting
tables
that
Thanks very much
By the way, is it necessary to drop temporary tables and or check for their
existence before creating them?
Thanks again ,
John.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
collecting
tables
that
it possible to grant the same prev to a group instead of individual users?
John.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:us9TIRa6DHA.3360@.tk2msftngp13.phx.gbl...
Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
collecting
tables
that
Temporary tables are dropped automatically when they go out of scope, which
means that if you create a temporary table in a stored procedure the
temporary table will be dropped when the stored procedure completes. In any
case, temporary tables are dropped when the user diconnects from the
database, and internally temporary tables created by different users have
different names, although they all seem to have the same name to the users,
so multiple users can create the same temporary table at the same time.
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:uOWIdea6DHA.2760@.TK2MSFTNGP09.phx.gbl...
their
I have a stored proc that runs and creates a temporary table for collecting
data and then drops it when it is done. My problem is that I can, as DBO,
run this fine but my users can not. How do I allow Create/Drop User tables
from a stored proc? Let me phrase that a different way; what kind of
permissions do I need to set up so that user's can run this stored proc that
creates/drops a temporary table? The stored proc already as the required
EXEC permissions for the user/groups to run it.
Thanks very much,
John.Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
quote:
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
quote:
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
quote:
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
quote:|||Hi,
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
quote:
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
quote:
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
quote:
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
quote:|||Excellent Idea! I completely forgot about that.
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>
Thanks very much
By the way, is it necessary to drop temporary tables and or check for their
existence before creating them?
Thanks again ,
John.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
quote:
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
quote:
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
quote:
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
quote:|||Thanks for the correct syntax; I was drawing a mind blank this morning. Is
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>
it possible to grant the same prev to a group instead of individual users?
John.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:us9TIRa6DHA.3360@.tk2msftngp13.phx.gbl...
Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
quote:
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
quote:
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
quote:
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
quote:|||Hi John,
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>
Temporary tables are dropped automatically when they go out of scope, which
means that if you create a temporary table in a stored procedure the
temporary table will be dropped when the stored procedure completes. In any
case, temporary tables are dropped when the user diconnects from the
database, and internally temporary tables created by different users have
different names, although they all seem to have the same name to the users,
so multiple users can create the same temporary table at the same time.
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:uOWIdea6DHA.2760@.TK2MSFTNGP09.phx.gbl...
quote:
> Excellent Idea! I completely forgot about that.
> Thanks very much
> By the way, is it necessary to drop temporary tables and or check for
their
quote:sql
> existence before creating them?
> Thanks again ,
> John.
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
> Hi John,
> Your users don't need any special permissions to create temporary tables,
> that is if you use real temporary tables, the ones prefixed with a #
> character. From your narrative I get the impression that you use permanent
> tables as temporary tables, but that is not advisable.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> collecting
DBO,[QUOTE]
> tables
> that
>
>
Grant create proc to
Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA
Grant create proc to
Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA
Grant create proc to
Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIAsql
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIAsql
Wednesday, March 21, 2012
Gradual Performance Degradation
Hi
I am using vb6, ADO 2.8 and SQL Server 2000.
Each time I run a stored proc (updates and inserts) I find that I get a 20%
increase in execution time, both through VB6 and Query Analyzer. If I
Disconnect and reconnect before executing the performance remains the same.
Any ideas where I should start looking.
RegardsYou should start by looking at the procedure definitions, objects, keys,
indexes, etc. Not necessarily in that order. If you post DDL and sample data
,
we can provide better help.
ML
http://milambda.blogspot.com/|||Thank you for reply, what I a looking for is an understanding of why the sp
execution time is consistant if I connect and disconnect in a loop and
increases if I keep the same connection . Its like DoEvents in VB. I think I
need some sort of flush command to SQL Server. The sp I run is a "HUB" whic
h
calls other functions and sp's there are no cursors or temporary tables. Th
e
T-SQL is hundreds of lines and not authored by myself. I have constructed a
simplistic version of the sp, but that runs with consistant execution times.
Also this sp is the only activity on the server.
Thanks again for reply
Regards
Martin
"ML" wrote:
> You should start by looking at the procedure definitions, objects, keys,
> indexes, etc. Not necessarily in that order. If you post DDL and sample da
ta,
> we can provide better help.
>
> ML
> --
> http://milambda.blogspot.com/|||Try executing the stored procedure multiple times in query analyzer and see
if you get the same performace degradation.
I suspect that the problem is with your VB code rather than the database.
You might try posting on a VB newsgroup to see what the recomended way is of
making multiple calls to the database. I know I have rewritten how my
connections are opened and closed as many as a dozen times to tune a VB
program doing a single insert 8000 times. My current code is using a
component for DB access, which opens and closes the connection every time it
is called, so I can't say exactly how I handled the connections after my
tuning.
"MartinT" <MartinT@.discussions.microsoft.com> wrote in message
news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> Hi
> I am using vb6, ADO 2.8 and SQL Server 2000.
> Each time I run a stored proc (updates and inserts) I find that I get a
20%
> increase in execution time, both through VB6 and Query Analyzer. If I
> Disconnect and reconnect before executing the performance remains the
same.
> Any ideas where I should start looking.
> Regards
>|||If your simplified version of the procedure does what you need, then do you
still need the old one? It might be a case of "parameter sniffing" (google
for the subject), or it may just as well be due to the complexity of the
procedure which over time takes more and more time to recompile. Hard to say
,
really, without seeing the code.
ML
http://milambda.blogspot.com/|||try WITH RECOMPILE option in your stored procedure and try executing it|||Been out of the office for couple of days .. I have run in QA with the same
degredation. Re-Start QA First time is fine then next x times gets slower. I
n
VB I have run with perfmon and only ever have one connection and the number
of times the connection is opened and closed are the number of time the code
requested the connections to be open / Closed.
Its a mystery.
Thanks for your reply
MartinT
"Jim Underwood" wrote:
> Try executing the stored procedure multiple times in query analyzer and se
e
> if you get the same performace degradation.
> I suspect that the problem is with your VB code rather than the database.
> You might try posting on a VB newsgroup to see what the recomended way is
of
> making multiple calls to the database. I know I have rewritten how my
> connections are opened and closed as many as a dozen times to tune a VB
> program doing a single insert 8000 times. My current code is using a
> component for DB access, which opens and closes the connection every time
it
> is called, so I can't say exactly how I handled the connections after my
> tuning.
>
> "MartinT" <MartinT@.discussions.microsoft.com> wrote in message
> news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> 20%
> same.
>
>|||Hmmm interesting one ... I will have a go and let you know.
Regards
MartnT
"Omnibuzz" wrote:
> try WITH RECOMPILE option in your stored procedure and try executing it
>|||Thanks will have a look and let you know.
Regards
Martin
"ML" wrote:
> If your simplified version of the procedure does what you need, then do yo
u
> still need the old one? It might be a case of "parameter sniffing" (google
> for the subject), or it may just as well be due to the complexity of the
> procedure which over time takes more and more time to recompile. Hard to s
ay,
> really, without seeing the code.
>
> ML
> --
> http://milambda.blogspot.com/
I am using vb6, ADO 2.8 and SQL Server 2000.
Each time I run a stored proc (updates and inserts) I find that I get a 20%
increase in execution time, both through VB6 and Query Analyzer. If I
Disconnect and reconnect before executing the performance remains the same.
Any ideas where I should start looking.
RegardsYou should start by looking at the procedure definitions, objects, keys,
indexes, etc. Not necessarily in that order. If you post DDL and sample data
,
we can provide better help.
ML
http://milambda.blogspot.com/|||Thank you for reply, what I a looking for is an understanding of why the sp
execution time is consistant if I connect and disconnect in a loop and
increases if I keep the same connection . Its like DoEvents in VB. I think I
need some sort of flush command to SQL Server. The sp I run is a "HUB" whic
h
calls other functions and sp's there are no cursors or temporary tables. Th
e
T-SQL is hundreds of lines and not authored by myself. I have constructed a
simplistic version of the sp, but that runs with consistant execution times.
Also this sp is the only activity on the server.
Thanks again for reply
Regards
Martin
"ML" wrote:
> You should start by looking at the procedure definitions, objects, keys,
> indexes, etc. Not necessarily in that order. If you post DDL and sample da
ta,
> we can provide better help.
>
> ML
> --
> http://milambda.blogspot.com/|||Try executing the stored procedure multiple times in query analyzer and see
if you get the same performace degradation.
I suspect that the problem is with your VB code rather than the database.
You might try posting on a VB newsgroup to see what the recomended way is of
making multiple calls to the database. I know I have rewritten how my
connections are opened and closed as many as a dozen times to tune a VB
program doing a single insert 8000 times. My current code is using a
component for DB access, which opens and closes the connection every time it
is called, so I can't say exactly how I handled the connections after my
tuning.
"MartinT" <MartinT@.discussions.microsoft.com> wrote in message
news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> Hi
> I am using vb6, ADO 2.8 and SQL Server 2000.
> Each time I run a stored proc (updates and inserts) I find that I get a
20%
> increase in execution time, both through VB6 and Query Analyzer. If I
> Disconnect and reconnect before executing the performance remains the
same.
> Any ideas where I should start looking.
> Regards
>|||If your simplified version of the procedure does what you need, then do you
still need the old one? It might be a case of "parameter sniffing" (google
for the subject), or it may just as well be due to the complexity of the
procedure which over time takes more and more time to recompile. Hard to say
,
really, without seeing the code.
ML
http://milambda.blogspot.com/|||try WITH RECOMPILE option in your stored procedure and try executing it|||Been out of the office for couple of days .. I have run in QA with the same
degredation. Re-Start QA First time is fine then next x times gets slower. I
n
VB I have run with perfmon and only ever have one connection and the number
of times the connection is opened and closed are the number of time the code
requested the connections to be open / Closed.
Its a mystery.
Thanks for your reply
MartinT
"Jim Underwood" wrote:
> Try executing the stored procedure multiple times in query analyzer and se
e
> if you get the same performace degradation.
> I suspect that the problem is with your VB code rather than the database.
> You might try posting on a VB newsgroup to see what the recomended way is
of
> making multiple calls to the database. I know I have rewritten how my
> connections are opened and closed as many as a dozen times to tune a VB
> program doing a single insert 8000 times. My current code is using a
> component for DB access, which opens and closes the connection every time
it
> is called, so I can't say exactly how I handled the connections after my
> tuning.
>
> "MartinT" <MartinT@.discussions.microsoft.com> wrote in message
> news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> 20%
> same.
>
>|||Hmmm interesting one ... I will have a go and let you know.
Regards
MartnT
"Omnibuzz" wrote:
> try WITH RECOMPILE option in your stored procedure and try executing it
>|||Thanks will have a look and let you know.
Regards
Martin
"ML" wrote:
> If your simplified version of the procedure does what you need, then do yo
u
> still need the old one? It might be a case of "parameter sniffing" (google
> for the subject), or it may just as well be due to the complexity of the
> procedure which over time takes more and more time to recompile. Hard to s
ay,
> really, without seeing the code.
>
> ML
> --
> http://milambda.blogspot.com/
Wednesday, March 7, 2012
Good book for learning the Stored Proc language
Though I have been using SQL Server for some years, it is just recently that I have need to learn how to write SP's. I would like a recommendation for a book that is rich with examples (my best learning style).
Many thanks...
Todd
I highly recommend two Microsoft Press books with Itzak Ben-Gan as the primary author.
Inside T-SQL Programming
Inside T-SQL Querying
Also with Microsoft Press, Burst/Forte's title: Programming SQL Server 2005
Friday, February 24, 2012
Global Variable in SQL Server
Hi,
I have a question on SQL Server.
How do I have a value that passing from application to Stored Proc. Then the variable will pass from stored proc to the trigger without storing into any table.
I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work. Anyone know any alternative?
Please help.
Thanks
If those passed parameters are already inserted/updated on your table then you can access those values from the trigger using INSERTED table. (Note Inserted table only accessable from the Trigger Scope & it will have the same table structure as the main table).
Inside Your Trigger:
Code Snippet
Declare @.SomeValue as Varchar(100);
Select @.SomeValue = SomeColumn From Inserted;
It is not good idea to use Gloabal Variable / Temp Table on triggers. You can't say the values always inserted from your SP.
|||No. the passed parameter I do not want to stored in the table due to some reason. How can I pass the value from stored proc to triggers without storing into table?
|||You cannot
without storing the data in the table.
pass the value from stored proc to triggers
All input data for the TRIGGER MUST exist within the TABLE that the TRIGGER fires on.
Of course, if you do not wish that the data be kept in the database for concern about security/visibility, you could have the TRIGGER set the field to NULL -thereby obliterating the data that was initially input.
|||Can we do something on tempdb? I mean can I stored the value into tempdb in stored proc then retrieve it from tempdb in triggers?|||According to you,
I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work.
Did it work?
|||The following example may help you...
Code Snippet
Create Table ThisIsIt (
[Id] int,
[Value] varchar(100))go
Create Table ThisIsLog (
[Users] varchar(100),
[When] datetime,
[Id] int,
[Operation] int,
[ValuesAffected] Varchar(8000))go
Create Trigger trg_ThisIsIt_logger on ThisIsIt For Insert
as
Begin
Declare @.ValuesAffected as Varchar(8000);
Declare @.User as varchar(100);
Declare @.Id as Int;Select @.ValuesAffected = '"?1";"?2"'
Select @.Id = Id, @.ValuesAffected = Replace(Replace(@.ValuesAffected,'?1', Id),'?2',Value)
From Inserted
If Exists(Select ID From tempdb..Sysobjects Where id = Object_id('tempdb..#Info'))
Select @.User = [User] from #Info;Insert Into ThisIsLog Values
(@.User, getdate(), @.Id, 1, @.ValuesAffected)
End
go
Create proc InsertThisIsIt
(
@.Id int,
@.Value varchar(100),
@.User varchar(100)
)
as
Begin
Create table #Info
(
[User] varchar(100)
)Insert Into #Info Values(@.User);
Insert Into ThisIsIt Values(@.Id, @.Value);
End
global temporary tables
When does a global temp table get dropped? i have a stored proc that creates
a global temp table and inserts a record into it.say for eg. i run the store
d
proc first, the temp table gets created.if another user runs the stored proc
now,the temp table is already there,hence the stored proc just uses the same
table and inserts a record into it.now if i end my session,will the temp
table still be available to the other user?
Actually what iam experiencing is, the temp table gets dropped if i end my
session even though another person is still using it.
Thanks in advance.Hi,
>From the BOL:
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing
them. The association between a task and a table is maintained only for
the life of a single Transact-SQL statement. This means that a global
temporary table is dropped at the completion of the last Transact-SQL
statement that was actively referencing the table when the creating
session ended.
HTH
Barry|||> Actually what iam experiencing is, the temp table gets dropped if i end my
> session even though another person is still using it.
Yes, that is correct. The life of a global temp table is the same as the
life of the session that started it. Which is one reason why they shouldn't
be used for sharing between concurrent users. If this is your intention,
use a real table!
A
a global temp table and inserts a record into it.say for eg. i run the store
d
proc first, the temp table gets created.if another user runs the stored proc
now,the temp table is already there,hence the stored proc just uses the same
table and inserts a record into it.now if i end my session,will the temp
table still be available to the other user?
Actually what iam experiencing is, the temp table gets dropped if i end my
session even though another person is still using it.
Thanks in advance.Hi,
>From the BOL:
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing
them. The association between a task and a table is maintained only for
the life of a single Transact-SQL statement. This means that a global
temporary table is dropped at the completion of the last Transact-SQL
statement that was actively referencing the table when the creating
session ended.
HTH
Barry|||> Actually what iam experiencing is, the temp table gets dropped if i end my
> session even though another person is still using it.
Yes, that is correct. The life of a global temp table is the same as the
life of the session that started it. Which is one reason why they shouldn't
be used for sharing between concurrent users. If this is your intention,
use a real table!
A
Global temp tables in SQL Server
Hi!
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-ParulA global temp table is like any other table.
How would you prevent users from overwriting data in a permanent table?
A better question is why are you using a global temporary table, and would local temporary tables suffice?|||I am using a global temp table because I have the following:
Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)
Is it possible to somehow lock these tables so the records inserted by one are not affected by another. Is there a way to use some kind of a userID?|||I am using a global temp table because I have the following:
Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)This does note require the use of "GLOBAL" temporary tables (prefixed with "##"). It only requires "LOCAL" temporary tables (prefixed with "#").|||Won't the variables lose scope if local temp tables are used?|||Not if one procedure calls the next. Then they are nested, and any temporary table declared in the outermost procedure exists until that procedure completes, making it available to the nested procedures.|||The downside to any temp table being used by nested procs is that each and every nested proc will recompile each time it is called. This could cause severe overhead in the execution time.|||ehhhhh...I've yet to see compile time become a factor outside of processes that are run hundreds of times per hour. And you'd have the same problem with global temporary tables.|||In any case....
Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction
Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls|||That should work, thanks!|||You should make a permanent table though...what for example, do you think would happen if you had a global table in existance and then tried to create it again?
Maybe you can explain to use what you are trying to do instead of trying to sledge hammer a solution with technology
Simple is usually the best answer|||while i am using a global table, if the stored proc is running in multiple sessions, it errors out saying "##temp already exists" or "it cannot be dropped."|||In any case....
Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction
Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
We gotta agree to disagree on this one. I doubt that a permanent table is an appropriate solution tohis problem, and adding the SPID to a global temporary table is just a hack for treating it like a local temporary table.|||so what else do you recommend i should try?|||Local.
Temporary.
Tables.|||Who's we?
And you mean respectfully diagree...you wouldn't want to get banned
In any case, I'd rather know what you are really trying to accomplish in the first place.
Also, it would be interesting to know how much data we are talking about.|||You lookin' fer a fight, Joisey-boy?
You are free to "diagree" with me, while'st I shall "disagree" with you (respectfully, as always).
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-ParulA global temp table is like any other table.
How would you prevent users from overwriting data in a permanent table?
A better question is why are you using a global temporary table, and would local temporary tables suffice?|||I am using a global temp table because I have the following:
Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)
Is it possible to somehow lock these tables so the records inserted by one are not affected by another. Is there a way to use some kind of a userID?|||I am using a global temp table because I have the following:
Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)This does note require the use of "GLOBAL" temporary tables (prefixed with "##"). It only requires "LOCAL" temporary tables (prefixed with "#").|||Won't the variables lose scope if local temp tables are used?|||Not if one procedure calls the next. Then they are nested, and any temporary table declared in the outermost procedure exists until that procedure completes, making it available to the nested procedures.|||The downside to any temp table being used by nested procs is that each and every nested proc will recompile each time it is called. This could cause severe overhead in the execution time.|||ehhhhh...I've yet to see compile time become a factor outside of processes that are run hundreds of times per hour. And you'd have the same problem with global temporary tables.|||In any case....
Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction
Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls|||That should work, thanks!|||You should make a permanent table though...what for example, do you think would happen if you had a global table in existance and then tried to create it again?
Maybe you can explain to use what you are trying to do instead of trying to sledge hammer a solution with technology
Simple is usually the best answer|||while i am using a global table, if the stored proc is running in multiple sessions, it errors out saying "##temp already exists" or "it cannot be dropped."|||In any case....
Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction
Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
We gotta agree to disagree on this one. I doubt that a permanent table is an appropriate solution tohis problem, and adding the SPID to a global temporary table is just a hack for treating it like a local temporary table.|||so what else do you recommend i should try?|||Local.
Temporary.
Tables.|||Who's we?
And you mean respectfully diagree...you wouldn't want to get banned
In any case, I'd rather know what you are really trying to accomplish in the first place.
Also, it would be interesting to know how much data we are talking about.|||You lookin' fer a fight, Joisey-boy?
You are free to "diagree" with me, while'st I shall "disagree" with you (respectfully, as always).
Global Stored Proc Find and Replace
Hello all,
Being a relative newbie to large scale MSSQL development, I'd like to try and find out if there is some sort of utility, command or stored proc that I can use to globally change all text within my database's stored procedures.
For example... I would like to change a table name from dbo.xtable... to dbo.ytable... Is this possible?
Actually, I'd be happy with some way to search through all my stored procs to find a specific string (i.e. xtable in the example above).
Any help provided will be greatly appreciated!
Jordan StradtmanDo you know how to scipt a database?
First off, all sprocs syhould be stored individually in .sql files, preferably in a version control app, or at least on a server...
To do them all script the db and do a search in notepad...|||Notepad?!
What a lame-ass app that is.
PFE32 dude.|||What's a PFE32?|||Programmer's File Editor. Search Google. It's freeware.
Lots and lots of goodies, such as templates, macros, etc. It's basic, small, love it.
Once you use it, you can never go back...|||Here they shoved down our throught this SlickEdit piece of s%$&, and deinstalled my TextPad. Well, thanks for the link, I'll go check it out.|||I should also mention that I have been using PFE for about 6+ years, which might be why I am so attached to it.|||Jordy,
In Enterprise Manager, right-click the database you wish to script, pick "All Tasks", then "Generate SQL Scripts". Click the button "Show All" to list the database objects, the move the objects you wish to script to the right window. There are some check boxes to assist in selecting various groups of object types.
After selecting what you want scripted, click the "Preview" button. When it appears with the scripts for the objects, click the "Copy" button, close up the windows, and paste the scripts into the text editor of your choice.
Stay warm - hope things are going well for you.
-- Greg H., Digital Marketing
Being a relative newbie to large scale MSSQL development, I'd like to try and find out if there is some sort of utility, command or stored proc that I can use to globally change all text within my database's stored procedures.
For example... I would like to change a table name from dbo.xtable... to dbo.ytable... Is this possible?
Actually, I'd be happy with some way to search through all my stored procs to find a specific string (i.e. xtable in the example above).
Any help provided will be greatly appreciated!
Jordan StradtmanDo you know how to scipt a database?
First off, all sprocs syhould be stored individually in .sql files, preferably in a version control app, or at least on a server...
To do them all script the db and do a search in notepad...|||Notepad?!
What a lame-ass app that is.
PFE32 dude.|||What's a PFE32?|||Programmer's File Editor. Search Google. It's freeware.
Lots and lots of goodies, such as templates, macros, etc. It's basic, small, love it.
Once you use it, you can never go back...|||Here they shoved down our throught this SlickEdit piece of s%$&, and deinstalled my TextPad. Well, thanks for the link, I'll go check it out.|||I should also mention that I have been using PFE for about 6+ years, which might be why I am so attached to it.|||Jordy,
In Enterprise Manager, right-click the database you wish to script, pick "All Tasks", then "Generate SQL Scripts". Click the button "Show All" to list the database objects, the move the objects you wish to script to the right window. There are some check boxes to assist in selecting various groups of object types.
After selecting what you want scripted, click the "Preview" button. When it appears with the scripts for the objects, click the "Copy" button, close up the windows, and paste the scripts into the text editor of your choice.
Stay warm - hope things are going well for you.
-- Greg H., Digital Marketing
Subscribe to:
Posts (Atom)