Database size question

I have a small application that I've developed using MSDE (2K). I'm
going to put this in to production by setting up a new computer and it
seems that Server 2005 Express edition will be adequate for our needs.
I want to check that the size of the databases I'm using are less than
4GB. How do I do that? I've looked around in SQL Server Enterprise
Manager but I don't see file sizes shown anywhere.
In Query Analyzer, try this:
EXECUTE sp_spaceused
In Enterprise Mangler, right click on the database, choose [Properties]. The
file size is in the middle of the page on the [General] tab,
Also, you can use Windows Explorer to view the database file. (It's not a
precise measure, but it's close.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
|||Thanks, Arnie - I don't know why I couldn't find that
My db's are all in the single-digit MB size, so I guess that the 4GB
limit of the Express version won't be an issue. And of course, if it
ever becomes an issue, we just have to upgrade.
Thanks again.
On Sat, 11 Nov 2006 09:10:03 -0800, "Arnie Rowland" <arnie@.1568.com>
wrote:

>In Query Analyzer, try this:
>EXECUTE sp_spaceused
>In Enterprise Mangler, right click on the database, choose [Properties]. The
>file size is in the middle of the page on the [General] tab,
>Also, you can use Windows Explorer to view the database file. (It's not a
>precise measure, but it's close.)
|||The maximum size of an MSDE database is 2GB so you can be sure your
databases are under 4GB
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.

Database Size Problem

Hello:
I am having a problem with a production database that has
run out of space. The database was configured by the
sofware manufacturer with 269mb of allocated space. The
current size is 450mb with 0% free space available. The
database has the Autogrow option turned on and is set for
Unrestricted file growth. There are 48GB of free space
available on the physical disk on which the database
resides. None of my users have reported any problems so
far.
I have a monitoring tool that is throwing up an alarm
because of this problem. I have shrank the database
several times, indicating that I want to have 10% free
space available after shrinking. Whenever I check the
database properties however, the sizes remain the same.
How can I correct this issue before it becomes a real
problem.
Thanks for any assistance
BrennanThis is a multi-part message in MIME format.
--=_NextPart_000_0427_01C3CE18.E52CA0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Well, 48GB is just about 10% of 450GB. I would start looking for more disk
space, unless you can actually purge data out of your database.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:00e101c3ce41$563d7450$a501280a@.phx.gbl...
Hello:
I am having a problem with a production database that has
run out of space. The database was configured by the
sofware manufacturer with 269mb of allocated space. The
current size is 450mb with 0% free space available. The
database has the Autogrow option turned on and is set for
Unrestricted file growth. There are 48GB of free space
available on the physical disk on which the database
resides. None of my users have reported any problems so
far.
I have a monitoring tool that is throwing up an alarm
because of this problem. I have shrank the database
several times, indicating that I want to have 10% free
space available after shrinking. Whenever I check the
database properties however, the sizes remain the same.
How can I correct this issue before it becomes a real
problem.
Thanks for any assistance
Brennan
--=_NextPart_000_0427_01C3CE18.E52CA0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Well, 48GB is just about 10% of =450GB. I would start looking for more disk space, unless you can actually =purge data out of your database.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:00e101c3ce41$56=3d7450$a501280a@.phx.gbl...Hello:I am having a problem with a production database that has run out of space. The database was configured by the sofware manufacturer =with 269mb of allocated space. The current size is 450mb with 0% =free space available. The database has the Autogrow option turned on and =is set for Unrestricted file growth. There are 48GB of free space =available on the physical disk on which the database resides. None of my =users have reported any problems so far.I have a monitoring tool that =is throwing up an alarm because of this problem. I have shrank =the database several times, indicating that I want to have 10% free =space available after shrinking. Whenever I check the database =properties however, the sizes remain the same.How can I correct this issue =before it becomes a real problem.Thanks for any assistanceBrennan

---

--=_NextPart_000_0427_01C3CE18.E52CA0B0--|||Hi Tom:
The DB is 450MB not 450GB. I have plenty of space
available on the volume. Total size is 90+GB and 48GB
are free.
Am I missing something?
Brennan
>--Original Message--
>Well, 48GB is just about 10% of 450GB. I would start
looking for more disk
>space, unless you can actually purge data out of your
database.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00e101c3ce41$563d7450$a501280a@.phx.gbl...
>Hello:
>I am having a problem with a production database that has
>run out of space. The database was configured by the
>sofware manufacturer with 269mb of allocated space. The
>current size is 450mb with 0% free space available. The
>database has the Autogrow option turned on and is set for
>Unrestricted file growth. There are 48GB of free space
>available on the physical disk on which the database
>resides. None of my users have reported any problems so
>far.
>I have a monitoring tool that is throwing up an alarm
>because of this problem. I have shrank the database
>several times, indicating that I want to have 10% free
>space available after shrinking. Whenever I check the
>database properties however, the sizes remain the same.
>How can I correct this issue before it becomes a real
>problem.
>Thanks for any assistance
>Brennan
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_04D6_01C3CE1E.D1605D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Oops, sorry about that. It looks then that you will soon reach a point
where if someone goes to add data, it will have to autogrow. All pending
inserts - and maybe some updates - will have to wait until that happens.
I'd just expand the database by, say, 100 MB, so that the users wouldn't
have to wait for the autogrow to complete.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<anonymous@.discussions.microsoft.com> wrote in message
news:010101c3ce47$61893e10$a601280a@.phx.gbl...
Hi Tom:
The DB is 450MB not 450GB. I have plenty of space
available on the volume. Total size is 90+GB and 48GB
are free.
Am I missing something?
Brennan
>--Original Message--
>Well, 48GB is just about 10% of 450GB. I would start
looking for more disk
>space, unless you can actually purge data out of your
database.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00e101c3ce41$563d7450$a501280a@.phx.gbl...
>Hello:
>I am having a problem with a production database that has
>run out of space. The database was configured by the
>sofware manufacturer with 269mb of allocated space. The
>current size is 450mb with 0% free space available. The
>database has the Autogrow option turned on and is set for
>Unrestricted file growth. There are 48GB of free space
>available on the physical disk on which the database
>resides. None of my users have reported any problems so
>far.
>I have a monitoring tool that is throwing up an alarm
>because of this problem. I have shrank the database
>several times, indicating that I want to have 10% free
>space available after shrinking. Whenever I check the
>database properties however, the sizes remain the same.
>How can I correct this issue before it becomes a real
>problem.
>Thanks for any assistance
>Brennan
>
>
--=_NextPart_000_04D6_01C3CE1E.D1605D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#d4d0c8>
<DIV><FONT face=3DTahoma size=3D2>Oops, sorry about that. It looks =then that you will soon reach a point where if someone goes to add data, it will =have to autogrow. All pending inserts - and maybe some updates - will have =to wait until that happens. I'd just expand the database by, say, 100 MB, =so that the users wouldn't have to wait for the autogrow to =complete.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT> </DIV>
<DIV> </DIV>
<DIV><<A href=3D"mailto:anonymous@.discussions.microsoft.com">anonymous@.discussions=.microsoft.com</A>> wrote in message <A href=3D"news:010101c3ce47$61893e10$a601280a@.phx.gbl">news:010101c3ce47$61=893e10$a601280a@.phx.gbl</A>...</DIV>Hi Tom:<BR><BR>The DB is 450MB not 450GB. I have plenty of =space <BR>available on the volume. Total size is 90+GB and 48GB <BR>are free.<BR><BR>Am I missing =something?<BR><BR>Brennan<BR><BR><BR>>--Original Message--<BR>>Well, 48GB is just about 10% of 450GB. I would =
start <BR>looking for more disk<BR>>space, unless you can =actually purge data out of your <BR>database.<BR>><BR>>--<BR>>Tom<BR>><BR>>--=---<BR>--<BR>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>>SQL Server MVP<BR>>Columnist, =SQL Server Professional<BR>>Toronto, ON Canada<BR>>www.pinnaclepublishing.com/sql<BR>><BR>><BR>>"Bren=nan" <<A href=3D"mailto:anonymous@.discussions.microsoft.com">anonymous@.discussions=.microsoft.com</A>> wrote in <BR>message<BR>>news:00e101c3ce41$563d7450$a501280a@.phx.gbl...<BR>>=Hello:<BR>><BR>>I am having a problem with a production database that has<BR>>run out =of space. The database was configured by the<BR>>sofware =manufacturer with 269mb of allocated space. The<BR>>current size is 450mb with 0% =free space available. The<BR>>database has the Autogrow option =turned on and is set for<BR>>Unrestricted file growth. There are 48GB of free space<BR>>available on the physical disk on which the database<BR>>resides. None of my users have reported any =problems so<BR>>far.<BR>><BR>>I have a monitoring tool that is throwing =up an alarm<BR>>because of this problem. I have shrank the database<BR>>several times, indicating that I want to have 10% free<BR>>space available after shrinking. Whenever I check the<BR>>database properties however, the sizes remain the same.<BR>><BR>>How can I correct this issue before it becomes a real<BR>>problem.<BR>><BR>>Thanks for any assistance<BR>><BR>>Brennan<BR>><BR>><BR>><BR>></BODY><=/HTML>
--=_NextPart_000_04D6_01C3CE1E.D1605D00--

Database Size - Allocated v. Used - Strange Problem

Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
PaulWell I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:
> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>|||Try DBCC UPDATEUSAGE next time and see if that helps.
--
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
>> Ok, I am seeing something weird.
>> I have a production database that according to EM and DBCC SHOWFILESTATS
>> has 140GB of space allocated and is currently using 138GB. However,
>> according to the OS, the data file is ~ 157GB. I also perform log
>> shipping with this DB and the size of the data file on the standby copy
>> is
>> correct @. 140GB. This tells me that the number OS seen size on the
>> production server is definitely incorrect. How can this be? What could
>> cause this sort of discrepancy?
>> The only thing that makes even a bit of sense.... the last mod date on
>> the data file is last week around the time I had a stupid vendor kick off
>> a select * into from a table with 35 million records. It is possible that
>> this caused an auto grow. The transaction never committed as I caught it
>> and rolled it back. Could the growth on the OS side have "stuck". The
>> numbers jive somewhat with what the auto growth rate is set at.
>> Regardless I am not ready to give up that much additional space right
>> now,
>> especially not knowing if the OS/SQL are chatting correctly, therefore
>> making what the OS sees actually available to SQL.
>> Any input would be greatly appreciated.
>> Thanks!
>> Paulsql

Database Size - Allocated v. Used - Strange Problem

Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
PaulWell I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:

> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
>

Database Size - Allocated v. Used - Strange Problem

Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
Paul
Well I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:

> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>
|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:

Database Restore Problem

I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.

After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]

There are no users connected to the database or processes running against it.

The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'

ALTER DATABASE DBA_pfaids_test SET MULTI_USER

Does this have something to do with the initial refresh from Production?
How can I get it to work?

Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.

You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.

Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.

Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.

Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)

~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).

Lex

Database Restore in Tran replication

Some problem happen from Appliction side & it was brought to my
attention that i have to restore the production database & same
production Database is used for transactional replication. i am not
sure if i restore the database it will break the replication or what
is better way to RESTORE. any help is appreciated & thanking in
advance.
If you restore to an earlier version of the production database, that means
that potentially you'll have non-convergence with the subscribers. You could
then do a comparison with a subscriber and decide which records to upload
from the subscriber, although this could end up being really complicated.
After that, I'd reinitialize the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks for your reply, that is what i thought. thanks for your help
again.

database restore from production to development

Hi,
I am in the process of overlaying our development data
with production backup. The users are different in prod as
compared with dev. My concern is if I restore production
copy on to dev, it might overwrite the sysusers info in
dev. I don't want to a) add prod users onto dev and b)
remove existing users in dev after the restore.
How do I keep my sysusers info intact ?
Currently I am thinking of
a) Get all users from dev and keep it aside
b) Restore the production onto dev
c) Delete all users in dev
d) Add users from (a) to dev
Will this work ? are there any other ways ?
TIA
MohanHave you looked at sp_change_users_login?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>|||Mo,
A way would be scripting out all database users on the development server
and run this script every time you restore the production backup. You can
create a sql agent job that restores the database and then runs this script
as an additional step.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>

database restore from production to development

Hi,
I am in the process of overlaying our development data
with production backup. The users are different in prod as
compared with dev. My concern is if I restore production
copy on to dev, it might overwrite the sysusers info in
dev. I don't want to a) add prod users onto dev and b)
remove existing users in dev after the restore.
How do I keep my sysusers info intact ?
Currently I am thinking of
a) Get all users from dev and keep it aside
b) Restore the production onto dev
c) Delete all users in dev
d) Add users from (a) to dev
Will this work ? are there any other ways ?
TIA
MohanHave you looked at sp_change_users_login?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>|||Mo,
A way would be scripting out all database users on the development server
and run this script every time you restore the production backup. You can
create a sql agent job that restores the database and then runs this script
as an additional step.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>

Database Restore caused production server reboot

I got a production server with the following hard ware software
Server: Dell PowerEdge 2950
Storage: FS SAN 650
Memory: 4GB
windows 2003 standard (sp1)
sql server 2005 standard (9.00.2153)
There are five user databases, their size range varies from 12GB to 15 GB
I wanted archived data from one of the databases, so started database
restore process from old backups that made server to freeze then rebooted it
automatically. I found the following messages in the event log. My question
is how a restore process will bring server shutdown. With the same memory
and i had 150 databases in dev environment and never got a server reboot
with database restore. Can you please let me know your expert ideas what
else might have caused the reboot if not the restore process.
Date 2/19/2008 11:15:48 AM
Log Windows NT (Application)
Source MSSQL$xxx
Category Logon
Event 3221242661
Computer xxxxxx
Message
SQL Server failed with error code 0xc0000000 to spawn a thread to process a
new login or connection. Check the SQL Server error log and the Windows
event logs for information about possible related problems. [CLIENT:
111.111.11.164]
Date 2/19/2008 11:22:09 AM
Log Windows NT (Application)
Source MSSQL$xxxxx
Category Logon
Event 3221242666
Computer xxxxxxx
Message
The server was unable to load the SSL provider library needed to log in; the
connection has been closed. SSL is used to encrypt either the login sequence
or all communications, depending on how the administrator has configured the
server. See Books Online for information on this error message: 0x2746.
[CLIENT: 111.111.111.5]
-- --
Thanks,
Subbu
Hi
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1308037&SiteID=1
http://www.sqlservermart.com/resources/SynAttackProtect.aspx
"Subbu" <subbudamireddy@.hotmail.com> wrote in message
news:%23g0IhlWdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I got a production server with the following hard ware software
> Server: Dell PowerEdge 2950
> Storage: FS SAN 650
> Memory: 4GB
> windows 2003 standard (sp1)
> sql server 2005 standard (9.00.2153)
> There are five user databases, their size range varies from 12GB to 15 GB
> I wanted archived data from one of the databases, so started database
> restore process from old backups that made server to freeze then rebooted
> it automatically. I found the following messages in the event log. My
> question is how a restore process will bring server shutdown. With the
> same memory and i had 150 databases in dev environment and never got a
> server reboot with database restore. Can you please let me know your
> expert ideas what else might have caused the reboot if not the restore
> process.
> Date 2/19/2008 11:15:48 AM
> Log Windows NT (Application)
> Source MSSQL$xxx
> Category Logon
> Event 3221242661
> Computer xxxxxx
> Message
> SQL Server failed with error code 0xc0000000 to spawn a thread to process
> a new login or connection. Check the SQL Server error log and the Windows
> event logs for information about possible related problems. [CLIENT:
> 111.111.11.164]
>
> ----
> Date 2/19/2008 11:22:09 AM
> Log Windows NT (Application)
> Source MSSQL$xxxxx
> Category Logon
> Event 3221242666
> Computer xxxxxxx
> Message
> The server was unable to load the SSL provider library needed to log in;
> the connection has been closed. SSL is used to encrypt either the login
> sequence or all communications, depending on how the administrator has
> configured the server. See Books Online for information on this error
> message: 0x2746. [CLIENT: 111.111.111.5]
> -- --
> Thanks,
> Subbu
>
>
>

Database Replication

Hello all,
I am looking for a "beginners guide" on setting up the automatic (daily)
replication of a production database to an identical database on another
server. This second database is to be used for training purposes. This is
for MSSQL 2000.
I have a customer that wishes to do this, and rather than explain it all I
would rather point him to a guide that he can look at (pleanty of pictures
would be good) and then ask questions if necessary.
Does anyone know of such a thing that is accessible?
Thank you in advance.
Jon Hunt
IT Manager
You might want to direct your customer to this -
http://www.mssqlcity.com/Articles/Re...TR/SetupTR.htm
There is some nonsense in here, but its ok. This is for SQL 7, but it is
much the same for SQL 2000.
"Jon Hunt" <thisisnotmyaddress@.nospam.invalid> wrote in message
news:Xns9578844C8F373softworks@.158.152.254.254...
> Hello all,
> I am looking for a "beginners guide" on setting up the automatic (daily)
> replication of a production database to an identical database on another
> server. This second database is to be used for training purposes. This is
> for MSSQL 2000.
> I have a customer that wishes to do this, and rather than explain it all I
> would rather point him to a guide that he can look at (pleanty of pictures
> would be good) and then ask questions if necessary.
> Does anyone know of such a thing that is accessible?
> Thank you in advance.
> --
> Jon Hunt
> IT Manager

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guys
David
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK =
N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guysDavid
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK =
N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guysDavid
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK = N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys

Database promotion life cycle

I need to review schema (table definition, constraints, etc.) inconsistencies
between development, test and production environments.
As part of our migration process to dot net, I've identified a number of
inconsistencies that are not typical of the promotion life-cycle. These
issues appear to be the result of an inadequate promotion process, during
which many schema changes have not been propagated. Conversely, there seem
to be many schema elements in production or QA that have not gone through the
life-cycle, instead having been implemented directly, rather than promoted.
Is there a document out there that can at least be used as a template to get
my review started? I'm certain this isn't anything new to businesses across
this country.SQL WHEEL wrote:
> I need to review schema (table definition, constraints, etc.) inconsistencies
> between development, test and production environments.
> As part of our migration process to dot net, I've identified a number of
> inconsistencies that are not typical of the promotion life-cycle. These
> issues appear to be the result of an inadequate promotion process, during
> which many schema changes have not been propagated. Conversely, there seem
> to be many schema elements in production or QA that have not gone through the
> life-cycle, instead having been implemented directly, rather than promoted.
> Is there a document out there that can at least be used as a template to get
> my review started? I'm certain this isn't anything new to businesses across
> this country.
>
Couple of suggestions:
1. Use a product like SQL Compare to determine what the differences are
between your environments
2. Starting immediately, do not allow direct modification of database
objects. Every object (table, sproc, trigger, etc) should be scripted
and stored in a version control system. Future modifications and new
objects MUST be done ONLY through version control. To modify an object,
you would first "check out" the script from version control, make the
necessary modifications, check the new script in, THEN deploy to the
development environment. Scripts for new objects are checked in, then
deployed. You can then use the capabilities of your version control
system to generate a changelog, making it easier to "release"
enhancements to testing, and finally production.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I use SQL Compare already to compare DTS, schema a data. We don't have MS
Source Safe in house at present. What is done is the scripts are sent to me
and deployed in DEV. The real problem is that b/c we're on a rapid
development track to get our legacy apps on dot net, I receive scripts both
for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
environments do not match PROD. Main reason there is b/c PROD has cross
server calls whereas DEV/TEST are self contained.
I want to put together a best practices doc at least the framework of one to
move forward from this mess. Is there a link out there where someone has
already done some leg work that I can take and modify to make it applicable
to our environment?
"Tracy McKibben" wrote:
> SQL WHEEL wrote:
> > I need to review schema (table definition, constraints, etc.) inconsistencies
> > between development, test and production environments.
> > As part of our migration process to dot net, I've identified a number of
> > inconsistencies that are not typical of the promotion life-cycle. These
> > issues appear to be the result of an inadequate promotion process, during
> > which many schema changes have not been propagated. Conversely, there seem
> > to be many schema elements in production or QA that have not gone through the
> > life-cycle, instead having been implemented directly, rather than promoted.
> >
> > Is there a document out there that can at least be used as a template to get
> > my review started? I'm certain this isn't anything new to businesses across
> > this country.
> >
> Couple of suggestions:
> 1. Use a product like SQL Compare to determine what the differences are
> between your environments
> 2. Starting immediately, do not allow direct modification of database
> objects. Every object (table, sproc, trigger, etc) should be scripted
> and stored in a version control system. Future modifications and new
> objects MUST be done ONLY through version control. To modify an object,
> you would first "check out" the script from version control, make the
> necessary modifications, check the new script in, THEN deploy to the
> development environment. Scripts for new objects are checked in, then
> deployed. You can then use the capabilities of your version control
> system to generate a changelog, making it easier to "release"
> enhancements to testing, and finally production.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||SQL WHEEL wrote:
> I use SQL Compare already to compare DTS, schema a data. We don't have MS
> Source Safe in house at present. What is done is the scripts are sent to me
> and deployed in DEV. The real problem is that b/c we're on a rapid
> development track to get our legacy apps on dot net, I receive scripts both
> for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
> environments do not match PROD. Main reason there is b/c PROD has cross
> server calls whereas DEV/TEST are self contained.
> I want to put together a best practices doc at least the framework of one to
> move forward from this mess. Is there a link out there where someone has
> already done some leg work that I can take and modify to make it applicable
> to our environment?
>
Your first step needs to get the environments configured the same so
that you can deploy script X to dev, confirm it works, then deploy
UNMODIFIED script X to test, test it, then deploy UNMODIFIED script X to
production. Create self-referencing linked servers in Dev and Test with
the same names that Prod uses, then your scripts don't have to be modified.
Your second step needs to be to establish a FIRM rule that NOTHING goes
directly into production, the process needs to be DEV -> TEST -> PROD,
no exceptions. Until you do that, you don't have a snowball's chance of
keeping things under control.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Database promotion life cycle

I need to review schema (table definition, constraints, etc.) inconsistencie
s
between development, test and production environments.
As part of our migration process to dot net, I've identified a number of
inconsistencies that are not typical of the promotion life-cycle. These
issues appear to be the result of an inadequate promotion process, during
which many schema changes have not been propagated. Conversely, there seem
to be many schema elements in production or QA that have not gone through th
e
life-cycle, instead having been implemented directly, rather than promoted.
Is there a document out there that can at least be used as a template to get
my review started? I'm certain this isn't anything new to businesses across
this country.SQL WHEEL wrote:
> I need to review schema (table definition, constraints, etc.) inconsistenc
ies
> between development, test and production environments.
> As part of our migration process to dot net, I've identified a number of
> inconsistencies that are not typical of the promotion life-cycle. These
> issues appear to be the result of an inadequate promotion process, during
> which many schema changes have not been propagated. Conversely, there see
m
> to be many schema elements in production or QA that have not gone through
the
> life-cycle, instead having been implemented directly, rather than promoted
.
> Is there a document out there that can at least be used as a template to g
et
> my review started? I'm certain this isn't anything new to businesses acro
ss
> this country.
>
Couple of suggestions:
1. Use a product like SQL Compare to determine what the differences are
between your environments
2. Starting immediately, do not allow direct modification of database
objects. Every object (table, sproc, trigger, etc) should be scripted
and stored in a version control system. Future modifications and new
objects MUST be done ONLY through version control. To modify an object,
you would first "check out" the script from version control, make the
necessary modifications, check the new script in, THEN deploy to the
development environment. Scripts for new objects are checked in, then
deployed. You can then use the capabilities of your version control
system to generate a changelog, making it easier to "release"
enhancements to testing, and finally production.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I use SQL Compare already to compare DTS, schema a data. We don't have MS
Source Safe in house at present. What is done is the scripts are sent to me
and deployed in DEV. The real problem is that b/c we're on a rapid
development track to get our legacy apps on dot net, I receive scripts both
for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
environments do not match PROD. Main reason there is b/c PROD has cross
server calls whereas DEV/TEST are self contained.
I want to put together a best practices doc at least the framework of one to
move forward from this mess. Is there a link out there where someone has
already done some leg work that I can take and modify to make it applicable
to our environment?
"Tracy McKibben" wrote:

> SQL WHEEL wrote:
> Couple of suggestions:
> 1. Use a product like SQL Compare to determine what the differences are
> between your environments
> 2. Starting immediately, do not allow direct modification of database
> objects. Every object (table, sproc, trigger, etc) should be scripted
> and stored in a version control system. Future modifications and new
> objects MUST be done ONLY through version control. To modify an object,
> you would first "check out" the script from version control, make the
> necessary modifications, check the new script in, THEN deploy to the
> development environment. Scripts for new objects are checked in, then
> deployed. You can then use the capabilities of your version control
> system to generate a changelog, making it easier to "release"
> enhancements to testing, and finally production.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||SQL WHEEL wrote:
> I use SQL Compare already to compare DTS, schema a data. We don't have MS
> Source Safe in house at present. What is done is the scripts are sent to
me
> and deployed in DEV. The real problem is that b/c we're on a rapid
> development track to get our legacy apps on dot net, I receive scripts bot
h
> for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
> environments do not match PROD. Main reason there is b/c PROD has cross
> server calls whereas DEV/TEST are self contained.
> I want to put together a best practices doc at least the framework of one
to
> move forward from this mess. Is there a link out there where someone has
> already done some leg work that I can take and modify to make it applicabl
e
> to our environment?
>
Your first step needs to get the environments configured the same so
that you can deploy script X to dev, confirm it works, then deploy
UNMODIFIED script X to test, test it, then deploy UNMODIFIED script X to
production. Create self-referencing linked servers in Dev and Test with
the same names that Prod uses, then your scripts don't have to be modified.
Your second step needs to be to establish a FIRM rule that NOTHING goes
directly into production, the process needs to be DEV -> TEST -> PROD,
no exceptions. Until you do that, you don't have a snowball's chance of
keeping things under control.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Database Permissions

Hello All,
I have a need to set up database security on our QA and Production servers
in the following manner:
IT Managers - Read/write access. Ability to view/start/stop scheduled jobs
not owned by them (all jobs are owned by sa).
Team Leads - Allow them to create/drop/alter stored procedures and functions
only. Otherwise, read-only access to all other objects
Developers - Read-only access to all objects.
For the IT Managers, I have a couple of options. 1) Give dbo permissions,
which will give them everything but the ability to view/start/stop jobs. I
won't give them sysadmin rights.
For the Developers, it's pretty easy. db_datareader permissions,
db_denydatawriter permissions.
For the Team Leads, I have not come up with anything bullet-proof. If I
give db_ddladmin rights, it allows them to modify data regardless of any
explicit deny permissions I put on any objects.
Does anyone have any suggestions?
Thanks,
David Grau
Database Administrator
Surprise & DelightDavid
1. Create ITManagers Group and add it to sysadmin server role.
2. Create TeamLead Group
a) Don't make it a member of sysadmin server role
b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
TeamLead
Take a look at creating Roles in the BOL as well
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
> Hello All,
> I have a need to set up database security on our QA and Production servers
> in the following manner:
> IT Managers - Read/write access. Ability to view/start/stop scheduled
> jobs
> not owned by them (all jobs are owned by sa).
> Team Leads - Allow them to create/drop/alter stored procedures and
> functions
> only. Otherwise, read-only access to all other objects
> Developers - Read-only access to all objects.
> For the IT Managers, I have a couple of options. 1) Give dbo permissions,
> which will give them everything but the ability to view/start/stop jobs.
> I
> won't give them sysadmin rights.
> For the Developers, it's pretty easy. db_datareader permissions,
> db_denydatawriter permissions.
> For the Team Leads, I have not come up with anything bullet-proof. If I
> give db_ddladmin rights, it allows them to modify data regardless of any
> explicit deny permissions I put on any objects.
> Does anyone have any suggestions?
> Thanks,
> David Grau
> Database Administrator
> --
> Surprise & Delight|||Thanks for your reply. However, let me add more detail now that I know more
about this request.
The IT Managers want to have SQL Logins that have expanded security beyond
their Windows logins. Is there a way to give them read/write to each
database as well as the ability to start/stop/delete scheduled jobs?
Similarly, Team Leaders want separate SQL Logins that they can use that have
the following: read-only access to the databases; create/drop/alter stored
procedures and functions. No other abilities for the Team Leaders. They
should not be able to create/alter/drop tables or any other objects.
Can all this be accomplished through database roles?
Thanks,
David Grau
--
Surprise & Delight
"Uri Dimant" wrote:

> David
> 1. Create ITManagers Group and add it to sysadmin server role.
> 2. Create TeamLead Group
> a) Don't make it a member of sysadmin server role
> b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
> TeamLead
>
> Take a look at creating Roles in the BOL as well
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
>
>|||David
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
Add them to sysadmin server role
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:3079CDD8-49BC-4466-9FC2-2CED09181265@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. However, let me add more detail now that I know
> more
> about this request.
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
> Similarly, Team Leaders want separate SQL Logins that they can use that
> have
> the following: read-only access to the databases; create/drop/alter
> stored
> procedures and functions. No other abilities for the Team Leaders. They
> should not be able to create/alter/drop tables or any other objects.
> Can all this be accomplished through database roles?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Uri Dimant" wrote:
>