Correct Setup for SQL Server 2005 for web project!

Ive been trying to get some type of Blogpost tutorial Etc on how to set up SQL Server 2005 to serve data to a website

1 How do I setup users?
a) Can I have 3 roles?
1a) Owner of DB can read/write

2a) reader Can Only read from database

3a) Writer. Can only write to database

How would I set this up? How can I call all these from ASP.NET depending on what the user is currently using on the website?

eg:

Just serving pages with content (reader)
Forms (writter)
admin (owner)

I also need to have the SQL keep sessions (Ive already ran aspnet_reqSQL.exe) and created all that im just unsure what type user can access all this

Any tutorials on how to set up a whole WEb application project from DB to VS 2005?

Thanks

Bump

No one knows how to correctly setup SQL Server?

|||

So no one know how to setup SQL Server 2005 efficiently and securely???????

*Sight*

|||

a) Can I have 3 roles?
1a) Owner of DB can read/write

2a) reader Can Only read from database

3a) Writer. Can only write to database

After you have installed sql server,you can create some user accounts and assign them permissions as you like. Open sql server-> select one database and expand-->select "security"--> click "Users" or "Roles"

sql

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!
nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegro ups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

copying/ backing up a database

Hi. im shortly going to have to submit my project for uni which ive created using sql server. How can i copy everything that ive made so i can submit everything and it can be replicated if necessary. Do i use the backup database task in enterprise manager or do i have to do that and export data or..?

ive used tables and stored procedures and a diagram btw.

thanks for any advice

Look up sp_detach_db in Books Online. Detach the database, copyit, and submit it. Then they can re-attach it using sp_attach_db.

sql

Copying Permissions on SPs and Views in Merge Rep

Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:

> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>

copying freextex indexes into multiple destination dbs on same dest server

Hi -
I've looked on Microsoft's web site:
http://support.microsoft.com/default...;EN-US;Q240867
and not yet been able to find a full answer to my question.
I'd appreciate any insight from others:
I have a database that has full text indexes on it, that I need to log ship
to multiple other (destination) servers. Also, to promote availability on
each of the destination servers, I plan to restore into two databases:
dest_db and dest_db_alt. Both are to have the same content, but different db
names. I intend to restore one set of dbs/logs from the source server into
standby mode in each destination database, sequentially, so that, with
proper client re-direction, a client hitting the destination server will
always get redirected to a read-only db in standby mode. While one db is
restoring, the other is readable. Ok, fair enough.
Now, the tricky part. The source db is freextex indexed, and one can't
build/rebuild freetext indexes on a db in standby or read-only mode. So,
apparently I'll have to copy the freetext index directory trees from the
source server and restore them on the destination server. What I'm uncertain
about is whether I'll be able to:
a) Use one set of freetext indexes files for both destination dbs (I don't
think so, If I understand things correctly),
b) Properly set the registry keys and directory/file names on the freetext
catalogs on the destination server so that even though at least one of the
destination dbs (necessarily) has a different dbid than the source database,
it will still be able to have a functioning freetext index. From the
generally related material I read it seems as if the registry keys map the
freetext catalog path(s) into SQL Server, and those catalog paths use dbids
and ftcatids integrated into their folder names to map a set of catalog
files to a dbid.
So here is my question: Can I just make multiple copies of the freetext
catalog files, differing only in the dbid portion of the folder name, and
then, using registry keys, map the newly created Freetext catalog folder(s)
into SQL Server with the relevant dbids on the destination server ?
Are dbids, or other database specific unique identifiers, hard coded within
the freetext catalog files themselves ?
Is there other important information I have to consider here ?
Thanks
Steve
Use replication for this. Create your catalogs and full text indexes using a
post snapshot script or create the tables and catalogs in advance, full text
index the tables, and then configure your article to delete the data not
drop and recreate the table (in the article properties section select the
browse button to the right of your table name, and in the snapshot tab, in
the name conflicts section , select delete all data).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve" <Steve_a013452@.newsgroups.nospam> wrote in message
news:OJ9F4IqGGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi -
> I've looked on Microsoft's web site:
> http://support.microsoft.com/default...;EN-US;Q240867
> and not yet been able to find a full answer to my question.
> I'd appreciate any insight from others:
> I have a database that has full text indexes on it, that I need to log
> ship to multiple other (destination) servers. Also, to promote
> availability on each of the destination servers, I plan to restore into
> two databases: dest_db and dest_db_alt. Both are to have the same content,
> but different db names. I intend to restore one set of dbs/logs from the
> source server into standby mode in each destination database,
> sequentially, so that, with proper client re-direction, a client hitting
> the destination server will always get redirected to a read-only db in
> standby mode. While one db is restoring, the other is readable. Ok, fair
> enough.
> Now, the tricky part. The source db is freextex indexed, and one can't
> build/rebuild freetext indexes on a db in standby or read-only mode. So,
> apparently I'll have to copy the freetext index directory trees from the
> source server and restore them on the destination server. What I'm
> uncertain about is whether I'll be able to:
> a) Use one set of freetext indexes files for both destination dbs (I don't
> think so, If I understand things correctly),
> b) Properly set the registry keys and directory/file names on the freetext
> catalogs on the destination server so that even though at least one of the
> destination dbs (necessarily) has a different dbid than the source
> database, it will still be able to have a functioning freetext index. From
> the generally related material I read it seems as if the registry keys map
> the freetext catalog path(s) into SQL Server, and those catalog paths use
> dbids and ftcatids integrated into their folder names to map a set of
> catalog files to a dbid.
> So here is my question: Can I just make multiple copies of the freetext
> catalog files, differing only in the dbid portion of the folder name, and
> then, using registry keys, map the newly created Freetext catalog
> folder(s) into SQL Server with the relevant dbids on the destination
> server ?
> Are dbids, or other database specific unique identifiers, hard coded
> within the freetext catalog files themselves ?
> Is there other important information I have to consider here ?
> Thanks
> Steve
>

Copying database to same machine?

Hi all,
I've read a lot of posts about copying a SQL database to a separate machine;
but, what about to the same machine.
In practice for others who have done what is the simplest reliable method?
Also, is there any reason not to detach the database, copy the physical
files (mdf and log) to a different name and reattach it under that other name?
Thanks!
gman997 wrote:
> Hi all,
> I've read a lot of posts about copying a SQL database to a separate
> machine; but, what about to the same machine.
> In practice for others who have done what is the simplest reliable
> method?
> Also, is there any reason not to detach the database, copy the
> physical
> files (mdf and log) to a different name and reattach it under that
> other name?
> Thanks!
If you need this totally scripted, you should create the new database and
use backup/restore. If you don't mind taking the database offline and making
a copy, you can use detach/attach. Detach is probably faster if your
database can be taken offline. In any case, I would perform a full backup
before doing anything, and in that respect, it might just pay to restore to
a new database...
David Gugick
Quest Software

Copying a database...

I've used the backup/restore method of copying a database. The only problem
is that all the tables aren't getting copied! There are supposed to be 74
tables but only 60 are coming across--I haven't checked other kinds of
objects.
I've done it twice with the same result. I've been very careful to select
full backup when generating the *.bak file. Any ideas? Please?
Mike...
"...after all He's not a tame lion..."
mporter (mporter@.discussions.microsoft.com) writes:
> I've used the backup/restore method of copying a database. The only
> problem is that all the tables aren't getting copied! There are supposed
> to be 74 tables but only 60 are coming across--I haven't checked other
> kinds of objects.
> I've done it twice with the same result. I've been very careful to select
> full backup when generating the *.bak file. Any ideas? Please?
If you did:
BACKUP DATABASE db TO DISK = 'C:\mybackup.bak'
and there already was a a backup in that file, you did not overwrite it,
but you appended.
Do a RESTORE HEADERONLY on the backup file.
In the future, if you don't wish to keep the old backup, add WITH INIT
to the BACKUP command.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Hi,
Donot overwrite the media file or disk file.
from
killer

Copy table values between different database

Hi guys,
I've a problem...
I've a SQL Server database (DB1) with a table (T1) and I want to copy all
the values in T1 to the same table (T2) located on the SQL Server database
DB2.
I want to perform this task by using a stored procedure launched by the
server DB1.
How can I perform this task?
If I have the 2 table on the same db I'll do something like this:
SELECT ....
INTO T2
FROM T1 WHERE ....
but how can I write this by saying that the 2 tables are on differen
databases?
Thanks in advance for all that can help me!
Steve,
Use the database name as part of the object identifier. See "Using
Identifiers as Object Names" in BOL.
Example:
use northwind
go
select * into pubs..t from orders
select * from pubs..t
drop table pubs..t
go
AMB
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||Qualify the source or destination table (whichever is applicable) such as
dbname.owner.tablename
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||select ...
INTO DB2.owner.TableName
FROM DB1.owner.TableName
Your stored procedure needs appropriate permission on DB1 and DB2. Look for
ownership chains in BOL.
Ana
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||Thanks a lot guys... more easy than what I think
"Tibor Karaszi" wrote:

> Qualify the source or destination table (whichever is applicable) such as
> dbname.owner.tablename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
>
>

Copy SQL Server Row - Repost

Hello all,
I've reposted this message as I didn't make it to clear what i'm
trying to achive in an earlier post - my apologies.
I need to write a procedure that will SELECT a row in a table and
INSERT the values into the same table, however, one of the columns,
'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
it will try to INSERT a row with a key that has the same value. So, is
there a way to do this without having to specify every field in the
SELECT clause and auto incrementing the 'ID' value?
Thanks all,
JY
Jon
You WILL have to specify all columns except ID
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegr oups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>
|||> So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
No, you'll need to specify a column list. It's a Best Practice to specify
an explicit column list, even without the IDENTITY issue. Query
Analyzer/Management Studio allow you to script SELECTs so you don't need to
type the list manually.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegr oups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>