Restrict User Access in MSSQL 2000

Hi,

I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.

Thanks & Regards,

Gaurav

You cannot restrict the users in SQL Server 2000 from seeing the available databases (this can be done in SQL Server 2005 by revoking VIEW ANY DATABASE to the public server role).

Also, a database owner will be able to change the file size for the databases he owns; you cannot prevent this.

Thanks
Laurentiu

Restoring SQL2000 DB to SQLExpress on different machine

Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.

Restoring DB

Hi all,

I have a very old MSSQL data backup file.
I do not even remember that it was created in SQL7 or 2000.
Anyway, I'm trying to recreate DB from this backup.
Is it possible to create DB from backup file?

I tried restore from that file and SQL server complaining.
"The file on device '***' is not valid Microsoft tape format backup set."
Thanks
-JayKev (nospam@.msu.edu) writes:
> I have a very old MSSQL data backup file.
> I do not even remember that it was created in SQL7 or 2000.
> Anyway, I'm trying to recreate DB from this backup.
> Is it possible to create DB from backup file?
> I tried restore from that file and SQL server complaining.
> "The file on device '***' is not valid Microsoft tape format backup set."

Maybe it is so old that it is from SQL 6.5? In that case, you need SQL 6.5
to read it.

If you are using SQL 2000, you should be able to restore the database,
no matter if the backup was taken on SQL7 or SQL2000.

Does the error message actually say ***, or is that something you put
in?

The actual command, and the actual error message could give a clue if
you are happening to do something wrong.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, It might be 6.5.
'***' was name of my file.

Thank you.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns944872A36966Yazorman@.127.0.0.1...
> Kev (nospam@.msu.edu) writes:
> > I have a very old MSSQL data backup file.
> > I do not even remember that it was created in SQL7 or 2000.
> > Anyway, I'm trying to recreate DB from this backup.
> > Is it possible to create DB from backup file?
> > I tried restore from that file and SQL server complaining.
> > "The file on device '***' is not valid Microsoft tape format backup
set."
> Maybe it is so old that it is from SQL 6.5? In that case, you need SQL 6.5
> to read it.
> If you are using SQL 2000, you should be able to restore the database,
> no matter if the backup was taken on SQL7 or SQL2000.
> Does the error message actually say ***, or is that something you put
> in?
> The actual command, and the actual error message could give a clue if
> you are happening to do something wrong.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Restoring Database MSSQL 2000

Hi All
We restored a database but the user asscociated with that database is not being restored. Is there a way to do this?
Question: Is there a way to restore the complete database including the users.
Thanks
JamesThis happens because the login belongs to the master database while the user belongs to the database that was restored (maybe that's an oversimplification, but that's how I approach it).

There are a couple of things that you can do:
1. If there are just a few users, add the user (but don't associate it to the database). Then login to the database using Query Analyzer and run exec master.dbo.sp_change_users_login 'UPDATE_ONE', '[login]', '[login]'. This will re-associate your database user's SID with the SID for the master database login.

2. If there are many users, you can use the Transfer Logins task in DTS to transfer the logins from the original server (assuming its still there) to the new server. Then run sp_change_users_login in the automatic mode (see BOL).

3. Finally, you can bcp out the contents of the syslogins table. There's a utility somewhere (I can't remember where right now) to then re-import that data into the target server's syslogins table. Again, you will still have to run sp_change_users_login.

Or, you could just delete and recreate the user(s); but then you have to re-assign all the privileges/etc.

HTH and good luck,

hmscott|||In the last option as hmscott mentioned. Deleting users may need deleting from system table itself as it sometimes doesn't show users of restored database in enterprise manager

sp_configure "allow updates" , 1
go
Delete from db_name..sysusers where...
go
sp_configure "allow updates" , 0|||Put these two procedures in the master database on the source server. Run sp_help_revlogin in the master database on the source server. Then add the users appropriately on the destination server by running the script produced in the master database on the destination server. This will map the SID correctly for you so you don't have to monkey around with sp_change_users or dropping/recreating users.

Restoring Database in a clustered environment

Can someone tell me how to make a backup in a sql clustered environment (MSSQL 2000) and how to restore that backup in clustered environment (MSSQL2005)? A step by step backup and restore procedure is appreciated.

regardless of if the server the db is on is clustered or not, the same principles apply

backup database dbname to file = 'c:\backup.bak'

same goes with the restore

here is an article that will help:

http://www.sqlteam.com/item.asp?ItemID=26689

|||

Hi Tim,

Yes the db is on clustered.. do i need to backup each db on each node? or just backup the active node's db?

|||

If the cluster is active/active (two instances, one on each node), and there are databases on the 2nd node that you want to backup, then you'll likely need to back that db up as well. Otherwise, you can backup the database on the active node.

The thing about a clustered environment is that it can host multiple instances. These instances are mutually exclusive from each other, so you can treat them like they are different server instances, even though at the time they may be on one node. So, just backup the databases as you normally would as if it was a nonclustered environment.

HTH,

Tim

|||

To take this to the simplest case:

Take a 2 node cluster with 1 instance, and 1 user database:

There is only one physical copy of that database. At any point in time, it may be mounted on node "A" or node "B".

The instance name is always the same, regardless of which physical machine hosts it.

So, to back up the database, you run a backup job on whichever node is currently hosting the instance.

Ideally, your backups go to media which is accessable from all cluster nodes, so that restores are clean as well. (another shared disk, or a network share if perf isn't as critical)

Restore is just the same: Go to the node which has your instance currently hosted (or move the instance to the node that you're currently on), and run restore.

For more complex configurations, just take the above and iterate for each instance.

There are 3rd party backup products which operate over the network, which will automatically go to the node currently hosting a database (via the instance's virtual hostname) and do the backup/restore on the appropriate machine.