db_owner

Hello. What would be the best role assignment for a temporary consultant to
access one of our user created databases on our sql2k server to allow this
person to develop freely but only within this database and not any of our
other databases including the obvious system databases? Would it be ok to
give this consultant a windows domain login and assign it as a db_owner or
would assigning a combination of the other system roles within this db be
better?
Thanks in advance.Yes, db_owner will pretty much give the consultant full control but only
within that database.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
> Hello. What would be the best role assignment for a temporary consultant
> to access one of our user created databases on our sql2k server to allow
> this person to develop freely but only within this database and not any of
> our other databases including the obvious system databases? Would it be
> ok to give this consultant a windows domain login and assign it as a
> db_owner or would assigning a combination of the other system roles within
> this db be better?
> Thanks in advance.
>|||Thanks for your speedy confirmation Dan. Much appreciated :-)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
> Yes, db_owner will pretty much give the consultant full control but only
> within that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||I just created a Windows domain user something like 'DomainAUser\JoeTest'
and assigned him to the db_owner role to one of our user created databases.
When I set up an odbc or .udl using this Windows domain user credentials how
come this user is able to see our system databases and a couple other in the
default database drop down box which kind of is concerning.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
> Yes, db_owner will pretty much give the consultant full control but only
> within that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||All logins can access databases with the guest user enabled. This includes
sample and system databases but permissions in the system databases are
minimal.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:O0%23a8Y5NIHA.3852@.TK2MSFTNGP06.phx.gbl...
>I just created a Windows domain user something like 'DomainAUser\JoeTest'
>and assigned him to the db_owner role to one of our user created databases.
>When I set up an odbc or .udl using this Windows domain user credentials
>how come this user is able to see our system databases and a couple other
>in the default database drop down box which kind of is concerning.
> Thanks.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
>|||Is there a way to disable all of the 'guest' accounts in all of the system
and user databases since I noticed this Microsoft article recommends not to
remove the 'guest' account
http://support.microsoft.com/default.aspx/kb/315523
Thanks Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:740E18FB-57E3-4F74-84B4-8327509943F9@.microsoft.com...
> All logins can access databases with the guest user enabled. This
> includes sample and system databases but permissions in the system
> databases are minimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:O0%23a8Y5NIHA.3852@.TK2MSFTNGP06.phx.gbl...
>|||> Is there a way to disable all of the 'guest' accounts in all of the system
> and user databases since I noticed this Microsoft article recommends not
> to remove the 'guest' account
> http://support.microsoft.com/default.aspx/kb/315523
There is only one guest user ("guest") in the system databases, which is
required for proper operation. The guest user inherits only minimal
permissions from the public role so permissions are quite limited in master
and tempdb.
You might consider revoking public execute permissions on the msdb database
sp_add_job and sp_add_dtspackage if you want to prevent non-sysadmins from
creating jobs or saving DTS packages in msdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:Ozqp05DOIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Is there a way to disable all of the 'guest' accounts in all of the system
> and user databases since I noticed this Microsoft article recommends not
> to remove the 'guest' account
> http://support.microsoft.com/default.aspx/kb/315523
> Thanks Dan.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:740E18FB-57E3-4F74-84B4-8327509943F9@.microsoft.com...
>|||Interesting. Thanks Dan, much appreciated. Take cares.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4266998A-E270-473E-9D98-3A44908D1891@.microsoft.com...
> There is only one guest user ("guest") in the system databases, which is
> required for proper operation. The guest user inherits only minimal
> permissions from the public role so permissions are quite limited in
> master and tempdb.
> You might consider revoking public execute permissions on the msdb
> database sp_add_job and sp_add_dtspackage if you want to prevent
> non-sysadmins from creating jobs or saving DTS packages in msdb.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:Ozqp05DOIHA.1208@.TK2MSFTNGP05.phx.gbl...
>sql

DB size limit on SQL2k Personal Edition

Does the Personal Edition of SQL Server 2000 (bundled with Enterprise
Edition) have a limit on the database size? I know the Desktop Edition has
the 2 gig limit, I wonder if the Personal Edition is also limited at the same
size...
Thanks in advance...
The SQL Server 2000 Books Online topic on maximum capacities has a footnote
about the DB size limit, it only mentions the DB size limit for MSDE:
3 The data portion of a database cannot exceed 2 GB in size when using the
SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine
(MSDE) 1.0. The total size of the database, including log files, can exceed
2 GB provided the sum of the sizes of the data files remains 2 GB or lower.
The topic is at
http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx.
Personal Edition does share many of the other same limitations as MSDE (such
as the amount of RAM and number of CPUs), this is mentioned in the tables
lower in the same topic.
Personal Edition also shares the workload governor with MSDE 2000. For more
info, see this topic and the last three links in the topic:
http://msdn2.microsoft.com/en-us/library/aa224554(SQL.80).aspx
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
|||MSDE is a _LOT_ better than personal edition
I'd reccomend not using personal edition for anything
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:epV4fr0ZHHA.4888@.TK2MSFTNGP06.phx.gbl...
> The SQL Server 2000 Books Online topic on maximum capacities has a
footnote
> about the DB size limit, it only mentions the DB size limit for MSDE:
> 3 The data portion of a database cannot exceed 2 GB in size when using the
> SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine
> (MSDE) 1.0. The total size of the database, including log files, can
exceed
> 2 GB provided the sum of the sizes of the data files remains 2 GB or
lower.
> The topic is at
> http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx.
> Personal Edition does share many of the other same limitations as MSDE
(such
> as the amount of RAM and number of CPUs), this is mentioned in the tables
> lower in the same topic.
> Personal Edition also shares the workload governor with MSDE 2000. For
more
> info, see this topic and the last three links in the topic:
> http://msdn2.microsoft.com/en-us/library/aa224554(SQL.80).aspx
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

DB Mirroring VS. Clustered Failover Questions

We are planning to update our two node, active/passive clustered system to SQL 2005 from SQL2K. Would we be better off to use database mirroring rather than clustering with failover? I believe mirroring takes 3 systems rather than two (one for each mirror and one for monitoring), but can the monitor be a rather low powered PC?

Our current cluster uses two 4-processor Dells and an external PowerVault RAID array with fiber channel connections. Each server has two NICs, one for general network and one for heartbeat and a fiber channel card to connect to the external PowerVault.

Here are my questions:

1 - If I understand it correctly, mirroring uses just separate servers without the need for the rather expensive and complicated shared fiber channel array that must go between them. Do I still need a shared array or can I just use internal RAID arrays in each box?

2 - Are there any advantages to keeping our current system? Our current system has worked well, but the failover takes a couple of minutes. Is mirroring faster?

3 - Is there still a virtual IP address and network name that everyone connects to?

4 - What are the drawbacks?

5 - Can I run non-mirrored DBs on the same servers or are they really locked together tightly?

6 - How much different in configuration can the two boxes be? With the cluster, I really need duplicates in every way.

7 - Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?

Thanks for any of your experiences on this.

Biggest difference is probably the level at which each provides its redundancy...mirroring provides protection/avail at the database level, but clustering provides protection at the server instance level. Another big difference is that in mirroring, the principal and mirror servers are separate SQL Server instances with distinct names, but a SQL instance on a cluster gets a single virtual server name and IP that remains the same no matter what node of the cluster is hosting the instance.

If you need protection at the server level (i.e. multiple databases are required for application functionality, etc.), clustering is probably a more appropriate choice...but if you only need to provide availability for one database at a time, mirroring has a number of advantages.

Unlike clustering, mirroring does't require proprietary hardware and does't have a potential failure point with shared storage...mirroring also can probably bring the standby database into service much faster than any other high availability technology, and works well with new capabilities in ADO.NET and SQL Native Access Client for client-side failover.

Note that you can't use database mirroring within a single cluster, but you can definately consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be aware that because a cluster failover is probably longer than the timeout value on mirroring, a High Availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state.

HTH

|||

Thank you, that is helpful. I am wondering, if the mirroring is at the DB level, how does the application (in this case a three tier app with a thick-client VB app on the desktop communicating to a mid-tier portion on another server) know about the failure and manage to get connected to the mirror? Is it transparent to the application, as is the case with a cluster, or must some code be written to test some indication of primary vs. mirror state?

It sounds like there is no provision for a virtual-IP as we would get with clustering. Is that true?

Also, do we need to purchase Enterprise Edition of SQL 2005 for a two node, active/passive cluster arrangement, or will Standard Edition handle that type of setup?

|||

Take a look at this article, it should answer all those questions and more:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

DB Maintenance - Purging Old File feature

Hi,

I'm using SQL2K with SP4 2187. I have created a DB Maintenance wizard where the purging older than 1 day is set.

However, this feature seems not to be working, even if I tried two ways. Delete the scheduled job and recreate it - not successful, 2nd) delete the Maintenance Plan, still not successful.

Is this a bug or do I miss something here.Hi.

I've had the same issue. But with mine sometimes it works and sometimes it doesn't. I did notice that if there isn't enough room on the drive it will not delete the previous files.
What I did is create a job that deletes the backups everynight at 6pm. By 6pm the Enterprise backup has already taken the backups off of the server.
It's very simple. Create a batch file that does a del *.bak and call the batch file from the job step.

I hope this helps.|||Many times concerning automation system for deleting data, manual data deleting is good. In any case if something goes wrong you will be loss everything...|||We found that if you have a data base using the SIMPLE model but still do a transaction log backup then this feature may not work for ANY of the databases in the same maintenance plan. The number of backup files just grew and grew.|||I found that same issue with the transaction log bkup. Microsoft ...
If you have the time everyday and remember to do it, I guess a manual bkup is better.
On my end, we update our databases from our old cobol dos system with txt files so if we loose a day it's not a big deal. We can just reload the data.
The other option would be to talk to who ever is doing the enterprise bkups off of the server. Maybe they can move instead of copy the files.