Scripting a SQL database

1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:

> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>

Scripting a SQL database

1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?
Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>
|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:

> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.
|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland
|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland
|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>

Scripting a SQL database

1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>

Scripted delete rows

Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:

delete student
where student_id not in (select student_id from tblStudent)

The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.

Any Ideas are appreciated."rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1114613428.538202.213970@.f14g2000cwb.googlegr oups.com...
> Hi,
> I need to delete rows from my user tables dependant upon there non
> existence from another table:
> delete student
> where student_id not in (select student_id from tblStudent)
> The reasons is convoluted, simplest explanation is that our operational
> system allows the change of business keys. This wreaks havoc in the
> data warehouse.
> So, I'm look for help on how I can delete rows from tables that have a
> column STUDENT_ID. I'd like the script to search for the tables, then
> perform the delete.
> I don't know where information about user tables are stored, nor how to
> loop through the results to do the delete.
> Any Ideas are appreciated.

You can use a query like this to generate a script, then review it before
executing it:

select 'delete from ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
' where not exists (select student_id from dbo.tblStudent ts where ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
'.student_id = ts.student_id)'
from
INFORMATION_SCHEMA.COLUMNS
where
COLUMN_NAME = 'student_id' and
objectproperty(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), 'IsTable') = 1

See the INFORMATION_SCHEMA views in Books Online, as well as syscolumns,
sysobjects, and "Meta Data Functions".

Simon|||Simon,
Works like a champ and I learned something new!
Thanks
Robsql

Script User Roles

I have created a user role in my SQL 7 database. I would like to copy this
role to 8 other servers. I created the role using Enterprise Manager. Can
you advise how to query this out to SQL Script so that I could just run the
script on the other servers to create the role and then assign users as
required.In Enterprise Manager you can script database and select option to script
roles and permissions also. Then find in the generated script related T-SQL
by role name (starting from sp_addrole and below).
Martin Rakhmanov
"dtatham" <dtatham@.discussions.microsoft.com> wrote in message
news:79CC672A-B46A-4F2C-9CC1-F7DAE8772DB8@.microsoft.com...
>I have created a user role in my SQL 7 database. I would like to copy this
> role to 8 other servers. I created the role using Enterprise Manager.
> Can
> you advise how to query this out to SQL Script so that I could just run
> the
> script on the other servers to create the role and then assign users as
> required.
>

Script User Rights

Is there an easy way to capture existing user rights from a SQL Server
instance in a scripted format and simply run the script in a new SQL Server
instance to set all of the user privileges? Thank you.Hi ,
You could use the Enterprise Manager to Script user Rights , Right click on
the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab and
look at the security options.
Hope this helps
Cheers
Vishal Gandhi
"CR" wrote:

> Is there an easy way to capture existing user rights from a SQL Server
> instance in a scripted format and simply run the script in a new SQL Serve
r
> instance to set all of the user privileges? Thank you.
>
>|||Thanks. However, I get "[SQL-DMO]CreateFile error on
"servername.databasename.DP1' error when I try to generate script?
"Vishal Gandhi" <VishalGandhi@.discussions.microsoft.com> wrote in message
news:0C04E96C-CDD9-4F54-B5CA-C213CAD97127@.microsoft.com...
> Hi ,
> You could use the Enterprise Manager to Script user Rights , Right click
on
> the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab
and[vbcol=seagreen]
> look at the security options.
> Hope this helps
> Cheers
> Vishal Gandhi
>
> "CR" wrote:
>
Server[vbcol=seagreen]

Script to Set User Permissions to Insert and Update Only

Is there a script built into SQL Server that given a specific user and
database will change the user's permission to only insert and update
tables? (I'd like to avoid the task to manually having to do this using
the GUI.)
Alternatively, the user is part of the "Public" role. Is there a script
to change the Public role such that the condition above is true?
Thanks
O.B. wrote:
> Is there a script built into SQL Server that given a specific user and
> database will change the user's permission to only insert and update
> tables? (I'd like to avoid the task to manually having to do this
> using the GUI.)
> Alternatively, the user is part of the "Public" role. Is there a
> script to change the Public role such that the condition above is
> true?
> Thanks
If the group already has rights then revoke them:
Revoke Delete, Select on <table_name> From Public
If the group does not yet have rights, then grant them:
grant insert, update on whatever to public
However, to run some Update queries, you may need to grant Select
rights. For example:
Update dbo.MyTable Set Col1 = 10 -- works without SELECT grant
Update dbo.MyTable Set Col1 = 10 Where Col2 = 5 -- does not work withotu
SELECT grant
David Gugick
Quest Software
www.imceda.com
www.quest.com

Script to Set User Permissions to Insert and Update Only

Is there a script built into SQL Server that given a specific user and
database will change the user's permission to only insert and update
tables? (I'd like to avoid the task to manually having to do this using
the GUI.)
Alternatively, the user is part of the "Public" role. Is there a script
to change the Public role such that the condition above is true?
ThanksO.B. wrote:
> Is there a script built into SQL Server that given a specific user and
> database will change the user's permission to only insert and update
> tables? (I'd like to avoid the task to manually having to do this
> using the GUI.)
> Alternatively, the user is part of the "Public" role. Is there a
> script to change the Public role such that the condition above is
> true?
> Thanks
If the group already has rights then revoke them:
Revoke Delete, Select on <table_name> From Public
If the group does not yet have rights, then grant them:
grant insert, update on whatever to public
However, to run some Update queries, you may need to grant Select
rights. For example:
Update dbo.MyTable Set Col1 = 10 -- works without SELECT grant
Update dbo.MyTable Set Col1 = 10 Where Col2 = 5 -- does not work withotu
SELECT grant
David Gugick
Quest Software
www.imceda.com
www.quest.com

Script to Set User Permissions to Insert and Update Only

Is there a script built into SQL Server that given a specific user and
database will change the user's permission to only insert and update
tables? (I'd like to avoid the task to manually having to do this using
the GUI.)
Alternatively, the user is part of the "Public" role. Is there a script
to change the Public role such that the condition above is true?
ThanksO.B. wrote:
> Is there a script built into SQL Server that given a specific user and
> database will change the user's permission to only insert and update
> tables? (I'd like to avoid the task to manually having to do this
> using the GUI.)
> Alternatively, the user is part of the "Public" role. Is there a
> script to change the Public role such that the condition above is
> true?
> Thanks
If the group already has rights then revoke them:
Revoke Delete, Select on <table_name> From Public
If the group does not yet have rights, then grant them:
grant insert, update on whatever to public
However, to run some Update queries, you may need to grant Select
rights. For example:
Update dbo.MyTable Set Col1 = 10 -- works without SELECT grant
Update dbo.MyTable Set Col1 = 10 Where Col2 = 5 -- does not work withotu
SELECT grant
David Gugick
Quest Software
www.imceda.com
www.quest.com

Script to log/record the users who accessed my database

Hi,

I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.

Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.

Also, are there any things that I should take care with my database from outside users.

Thanks
-SudhakarThe only way I can think of to log user access (especially read access) is to constantly run a Profiler trace. This will very likely not be possible on a third party server, even if you have admin access to the server.
A lesser option is to have the third party turn on login successful auditing, which will put an entry in the errorlog every time someone logs into the server. This will not tell you what database they log into, nor will it tell you what they did there.sql

Script to kill all user connections to a db except three

Hi guru's
SQL 2000 Database needs all user connections terminated to it except
three. These need to be kept running for a job ran later on.
Is anyone aware of a script that will recursively go through all the
connections to a specific database and kill all processes except the
ones we want to remain intact ?
Please let me know if you need any more information
Thanks in advance
PaulYou can use the one at http://www.dbmaint.com/util_proc.asp as a starting po
int...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>|||ALTER DATABASE .... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>

Script to kill all user connections to a db except three

Hi guru's
SQL 2000 Database needs all user connections terminated to it except
three. These need to be kept running for a job ran later on.
Is anyone aware of a script that will recursively go through all the
connections to a specific database and kill all processes except the
ones we want to remain intact ?
Please let me know if you need any more information
Thanks in advance
Paul
ALTER DATABASE .... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegro ups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>

Script to kill all user connections to a db except three

Hi guru's
SQL 2000 Database needs all user connections terminated to it except
three. These need to be kept running for a job ran later on.
Is anyone aware of a script that will recursively go through all the
connections to a specific database and kill all processes except the
ones we want to remain intact ?
Please let me know if you need any more information
Thanks in advance
PaulYou can use the one at http://www.dbmaint.com/util_proc.asp as a starting point...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>|||ALTER DATABASE .... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>

script to kick all users off database

I am writing a script to delete all user tables from a SQL Server 2000
database and before I do that I want to kick all other users off the
database.
Can anyone give me the sql script that will do this?
Killing all the connections to the database you can use command:-
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Vishal,
That also puts it in single user mode, which might not be desirable.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vishal Parkar wrote:
> Killing all the connections to the database you can use command:-
> ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
>
|||Caroline,
This is what I use:
CREATE proc sp_dba_killscript
@.dbname sysname
as
select 'kill '+ convert(varchar(5),a.spid)
from master..sysprocesses a join master..sysdatabases b
on (a.dbid=b.dbid)
where b.name = @.dbname
Paste the result into your query window and run it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Caroline wrote:
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?
|||There is a nice script that Tibor wrote, that will send a message to
everyone logged in to the server. So you can before killing... His web site
is www.dbmaint.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Caroline" <carolinefryer@.gmail.com> wrote in message
news:eea6e29c.0411091656.1ee19f46@.posting.google.c om...
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?

script to kick all users off database

I am writing a script to delete all user tables from a SQL Server 2000
database and before I do that I want to kick all other users off the
database.
Can anyone give me the sql script that will do this?Killing all the connections to the database you can use command:-
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Vishal,
That also puts it in single user mode, which might not be desirable.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vishal Parkar wrote:
> Killing all the connections to the database you can use command:-
> ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
>|||Caroline,
This is what I use:
CREATE proc sp_dba_killscript
@.dbname sysname
as
select 'kill '+ convert(varchar(5),a.spid)
from master..sysprocesses a join master..sysdatabases b
on (a.dbid=b.dbid)
where b.name = @.dbname
Paste the result into your query window and run it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Caroline wrote:
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?|||There is a nice script that Tibor wrote, that will send a message to
everyone logged in to the server. So you can before killing... His web site
is www.dbmaint.com
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Caroline" <carolinefryer@.gmail.com> wrote in message
news:eea6e29c.0411091656.1ee19f46@.posting.google.com...
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?sql

script to kick all users off database

I am writing a script to delete all user tables from a SQL Server 2000
database and before I do that I want to kick all other users off the
database.
Can anyone give me the sql script that will do this?Killing all the connections to the database you can use command:-
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Vishal,
That also puts it in single user mode, which might not be desirable.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vishal Parkar wrote:
> Killing all the connections to the database you can use command:-
> ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
>|||Caroline,
This is what I use:
CREATE proc sp_dba_killscript
@.dbname sysname
as
select 'kill '+ convert(varchar(5),a.spid)
from master..sysprocesses a join master..sysdatabases b
on (a.dbid=b.dbid)
where b.name = @.dbname
Paste the result into your query window and run it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Caroline wrote:
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?|||There is a nice script that Tibor wrote, that will send a message to
everyone logged in to the server. So you can before killing... His web site
is www.dbmaint.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Caroline" <carolinefryer@.gmail.com> wrote in message
news:eea6e29c.0411091656.1ee19f46@.posting.google.com...
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?

Script to grant execute for sprocs

Hello.

I'm using what looks to be a popular script to grant execute privileges to stored procedures, and it works great as long as the user account that you want to grant to is not a domain account.

For example, I need to grant execute to myDomain\dbUsers, but get a syntax error when the script tries to execute this statement:

SET @.SQL = 'GRANT EXECUTE ON [' + @.Owner

+ '].[' + @.StoredProcedure

+ '] TO myDomain\dbUsers'

Incorrect syntax near '\'.

The script works fine if a non-concatenated user account is given.

We use Active Directory to manage our access, thus the domain\group.

Has anyone found a way around this?

Thanks in advance.

Tess

Here's the entire script for anyone who's interested:

USE whateverDatabase

GO

DECLARE @.SQL nvarchar(4000),

@.Owner sysname,

@.StoredProcedure sysname,

@.RETURN int

-- Cursor of all the stored procedures in the current database

DECLARE cursStoredProcedures CURSOR FAST_FORWARD

FOR

SELECT USER_NAME(uid) Owner, [name] StoredProcedure

FROM sysobjects

WHERE xtype = 'P'

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name)), 'IsMSShipped') = 0

AND name LIKE 'p%'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursStoredProcedures

INTO @.Owner, @.StoredProcedure

-- Set the return code to 0

SET @.RETURN = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@.@.FETCH_STATUS = 0) AND (@.RETURN = 0))

BEGIN

-- Create the SQL Statement. Since we’re giving

-- access to all stored procedures, we have to

-- use a two-part naming convention to get the owner.

SET @.SQL = 'GRANT EXECUTE ON [' + @.Owner

+ '].[' + @.StoredProcedure

+ '] TO myDomain\dbUsers'

-- Execute the SQL statement

EXEC @.RETURN = sp_executesql @.SQL

-- Get the next row

FETCH NEXT FROM cursStoredProcedures

INTO @.Owner, @.StoredProcedure

END

-- Clean-up after the cursor

CLOSE cursStoredProcedures

DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.

IF (@.RETURN = 0)

BEGIN

-- Exited fine, commit the permissions

COMMIT TRAN

END

ELSE

BEGIN

-- Exited with an error, rollback any changes

ROLLBACK TRAN

-- Report the error

SET @.SQL = 'Error granting permission to ['

+ @.Owner + '].[' + @.StoredProcedure + ']'

RAISERROR(@.SQL, 16, 1)

END

GO

Just like with table and other object names, if the user contains special characters it must be "quoted" so, add either [] or "" around the user

|||

Thank you very much!

Tess

sql

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanks
Hi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>