Granting a user permissions to create and Drop a table

How do I allow a user (or group of users) permission to create/drop a table?

I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well?
'GRANT DROP TABLE TO username' doesn't work?
and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.

thanksWhat I want to know as well is, if the table is DROPPED, do I have to re-set all permissions on it again after it is re-created?
If so, what is the best way to achieve all this/

Thanks|||

See permission section of the DROP TABLE article in BOL. Permissions would have to be reassigned - they are dropped when the table is dropped. You should script all your database operations and manage script files with a source code control product.

Thanks
Laurentiu

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegroups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.
Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegr oups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegroups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

GRANT statement and Windows login

I am trying to grant a Windows login rights to run a Profiler trace.
I'm using the following command:
GRANT ALTER TRACE to 'DOMAIN\User'
but I keep getting the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN\User'.
I've tried it without the quotes and still get an error.
What am I missing?
ThanksNever mind, I figured it out:
GRANT ALTER TRACE to [DOMAIN\User]

Grant Select

I am getting a syntax error in SQL 2005 with the command "Grant Select to user". I get "Incorrect syntax near the keyword 'to'". This worked in the previous version, and according to what I can find in the documentation, it should work in this version. I hope I don't have to specify each table individually. Anybody know any more about this?Why not add the user to db_datareader role?

GRANT PERMISSION TO ALL OBJECTS ON A DATABASE

How do I write T-SQL command to grant a permission to ALL objects in a
database.
Thanks.
Esmeralda
Esmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permiss?o de EXEC para o usuário especificado
CompatXvel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
--permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:

> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda

GRANT PERMISSION TO ALL OBJECTS ON A DATABASE

How do I write T-SQL command to grant a permission to ALL objects in a
database.
Thanks.
EsmeraldaEsmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProced
ure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permiss?o de EXEC para o usuário especificado
Compat_vel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:

> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda

GRANT PERMISSION TO ALL OBJECTS ON A DATABASE

How do I write T-SQL command to grant a permission to ALL objects in a
database.
Thanks.
EsmeraldaEsmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permissão de EXEC para o usuário especificado
Compatível com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda

Grant permission (Sch-M lock)

When I tried to grant a Select permission on a table to a
user, it was blocked by any users who were currently
reading that table.
The grant command in QA issued lock Sch-M (Schema
modification) lock. Why is it issued Sch-M lock ?
Is it mean I cannot grant permission only any table while
users access it ?Hi Johnny,
Changes to schema information can not occur while the object is in use. I
suggest you continue to try of schedule it over night.
As an alternative to using SQL users ID you could use Windows groups to
secure your tables or use Database roles and add your users to the role
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:058701c3b569$b81a9810$a301280a@.phx.gbl...
> When I tried to grant a Select permission on a table to a
> user, it was blocked by any users who were currently
> reading that table.
> The grant command in QA issued lock Sch-M (Schema
> modification) lock. Why is it issued Sch-M lock ?
> Is it mean I cannot grant permission only any table while
> users access it ?
>|||1) Why does grant permission on table require Sch-M lock ?
I am sure it is regular task to grant table permission to
users. I can't just wait for scheduling overnight.
2) I also tried to create a database role. But when I
grant Select permission to that role, it was blocked.
Running SQL 7 & SP3 on Windows 2000.|||Johnny,
1) You have a good point but the fact remain it does and your can do it
until you can lock the table. Think of it this way, if you were changing
the permissions to remove users (which may be accessing the Table) with
permissions. Then you can understand you would need to lock the schema.
The process doesn't know you are adding until after.
2) Yes the time you grant permissions to the role again you need a lock but
after what you do is add users to roles this doesn't require a lock at all.
So you can do this while the table is in use.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:049101c3b56e$5e304e80$a401280a@.phx.gbl...
> 1) Why does grant permission on table require Sch-M lock ?
> I am sure it is regular task to grant table permission to
> users. I can't just wait for scheduling overnight.
> 2) I also tried to create a database role. But when I
> grant Select permission to that role, it was blocked.
> Running SQL 7 & SP3 on Windows 2000.

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.
Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>
|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '' "
Please note that same name exist for login and database user i.e CORP\AppDev
.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '' "
> Please note that same name exist for login and database user i.e CORP\AppD
ev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
>

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today :)
"AndyP" wrote:
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
> > Hello,
> >
> > I am running a very simple command to grant a stored procedure an EXEC
> > permission to a login that is created for a NT group. The command is as
> > follows
> >
> > GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> >
> > I am getting the error like "Incorrect syntax near '\' "
> >
> > Please note that same name exist for login and database user i.e CORP\AppDev.
> >
> > Any help in this matter would be greatly appreciated.
> >
> >

grabbing a value from listbox to query database

hello forum,

I need to grab astring value from alist box in from a web form,
and pass it to a sql select command statement where that value is equal to
all values in a database table(sql 2000).

example

zip code list box
33154
33254
84578
85475
35454

selected value is 85475

I am putting that value in a string like this:

dim string_zip as string
string_zip = zip_ListBox.text

Question, how do i pass that value to sql stament, i am using this but does not work.

SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip =string_zip", SqlConnection1)

You should use this

SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip ='" &string_zip &"'", SqlConnection1)

Regards

|||

Actually, you should use this:

SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zip

Using the string concatenation method is a good way to get yourself hacked.

|||

Motley wrote:

Actually, you should use this:

SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zip

Using the string concatenation method is a good way to get yourself hacked.

Yes, this is preferred over my solution becuase it is more secure.

Thanks Motley

|||

Thanks for you help, it works, but I encounter another problem.

Problem:

multiple selection from list box is allowed, I am getting a string value from all selected choices like this:

Dim listofstringsAsString
Dim itemAs ListItem

ForEach itemIn listbox.Items
If item.SelectedThen
listofstrings = listofstrings & item.Text & ","
EndIf
Next

so i havelistofstrings = (selectedvalue1,selectedvalue2,selectedvalue3,......)

I need to select all values from a table in database where any of those values corresponds.

NOTE: Values in database can also be in the format of (value1,value2,value3,.....) or just a single (value1,)

|||

Search for messages on the UDF named "Split" one was posted recently.

SELECT *
FROM table
WHERE field IN (SELECT * FROM Split(@.listofstrings))

OR
SELECT *
FROM table
JOIN Split(@.listofstrings) s ON (table.field=s.id)

Going from osql to sqlcmd (MSDE to SqlExpress?)

I am familiar with loading a DB so that it will work under MSDE. This
is the command I use:
osql -S ServerName\InstanceName -E -i ScriptName.sql
So now I uninstalled MSDE and am using SqlExpress instead. To do the
same I tried:
Sqlcmd -S .\SQLExpress -i ScriptName.sql
and
Sqlcmd -S ServerName\SQLExpress -E -i ScriptName.sql
Which does not do anything except give me an erro stating that my DB
does not exist under SQLExpress. (Which is why I was trying to
install it )
Am I missing something? Do I need MSDE running on my toolbar?
How do I load a DB into SQLExpress?
Regards
oracle wrote:
> I am familiar with loading a DB so that it will work under MSDE. This
> is the command I use:
> osql -S ServerName\InstanceName -E -i ScriptName.sql
> So now I uninstalled MSDE and am using SqlExpress instead. To do the
> same I tried:
> Sqlcmd -S .\SQLExpress -i ScriptName.sql
> and
> Sqlcmd -S ServerName\SQLExpress -E -i ScriptName.sql
>
Those commands do not load a database. They connect to the specified SQL
Server instance. The SQL service is already running and the databases
are mounted and ready. It sounds like the problem you are having is that
SQL Express does not know about your MSDE databases. How did you move
the databases to SQL Express? If you didn't move them, you might be able
to use the "CREATE DATABASE database_name FOR ATTACH" command. If you
still have MSDE installed, first detach the databases using sp_detach_db
and copy them over the SQL Express data folder. I do not know if you can
do this from SQL Express Manager.
David Gugick
Quest Software
www.quest.com

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