How To Grant Backup And Restore ONLY To A Sqlserver Login

Apr 15, 2008

Hi guys!
 
When it comes to writing sql statements, I do ok.  But I just know the basics on sql server dba stuff.
I need to create a sql server login account that has the ability to backup and restore databases, BUT NOTHING ELSE.
No ability to change anything other than via the restore process.
 
I figured out how to do the backup portion of my requirement, but not the restore part.
Any ideas?
 
Thanks in advance!

 

View 3 Replies


ADVERTISEMENT

Can't Restore A Backup From File On Sqlserver 6.5

Oct 14, 1999

I have a production SQLServer 6.5 on Nt 3.51 SP4
that had problems two weeks ago, an I had
to restore the backup of the previous night.

we are testing our disaster recovery procedures
on another server, identical to the production
one.

We have installed NT 3.51 SP 4 and SQLServer 6.5
in the same order and with the same configuration
of the production server,
but i can't restore on the test server the
backup files that i have succesfully resotred
on the production server.

we make the SQLServer back up on file ( and
then we backup those files on tape with
NT backup) so I don't think is a problem
of HW Tape Bios or Compression because
i have tried to resotre directly the
files without taking them from a tape.

1)
I have created a new database , without data,
with the same devices, in megabyte, that I
have on the production server.
than from enterprise manager I have started
the restore from file, and after two seconds
everything stops, and I can't even shutdown
the task but i MUST turn the server off
using the power button.

when I start the server again there are
no specific errors in the event log nor
in the SQLServer error log.
The db i was trying to restore is marked
"loading" and it is not available.

This same procedure works perfectly
on the production server.

2)
I have then created another db on the test
server, and I have succesfully tranferred
( but not resotred) the produciont db
onto the test one, using the transfer menu
on SQLEnterprise manager.

3)
another strange thing is that I have tryed
to expand the tempdb, which is only 2 mega
by default, but whenever i try to expand it,
using enterprise manager, only the LOG area
of tempdb sucessfully expands, not the data area.
I don't know if this behaviour is related
to the unsuccesfull restore or if
it is another problem.

i have checked the sort order and character set
of the 2 servers and they are the same.

thanks in advance for any help.

Eugenio La Mesa
Publisoft
Italy

View 1 Replies View Related

Microsoft.SqlServer.Management.Smo Backup And Restore Classes

Jan 10, 2006

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

View 4 Replies View Related

Backup/restore Table Belonging To One User/login

Sep 16, 2007

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA
Table Customer with user / login CompanyB

Table Contact with user / login CompanyA
Table Contact with user / login CompanyB


I need to split these into two different database's

Database "CustomersA"
Table Customer with user / login CompanyA
Table Contact with user / login CompanyA

Database "CustomersB"
Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?


Ingar

View 5 Replies View Related

Grant Login To Another Database

Dec 1, 2007



Hi there,

I need help. I can mapped login to another database by MSSQLManagement studio. I need that in sql.

Security->Logins->Select LoginName->properties->user mapping->from the users mapped that login I select my database name and from the Database role membership db_owner and then click ok. I need that in sql.

thank now

View 2 Replies View Related

Role Of Login Specified In GRANT UNSAFE...

Feb 27, 2007

For registering UNSAFE assemblies, we have to sign the dll and register it in SQL 2005. Also, the permission needs to be granted to a login..

Example code;

CREATE ASYMMETRIC KEY MyKey FROM EXECUTABLE FILE = 'C:AssembliesInterop.SQLDMO.dll'

CREATE LOGIN MyLogin

FROM ASYMMETRIC KEY MyKey

GRANT UNSAFE ASSEMBLY To MyLogin

What is the purpose of creating this MyLogin and how it serves to access the unsafe assembly.

Can anyone throw more light on this.



Thanks

Baskar

View 1 Replies View Related

User Without Login : Grant Problem.

Jan 21, 2008

Hi to everybody,

I have a permission problems with user create for service.

I created an user without login in a database. I use this user for service broker activation procedure.So, this user have a certificate to exports in other database.

To generalize activation procedure of many service broker queue I try to use this piece of code :



DECLARE @QueueName nvarchar(MAX)
DECLARE @queue_id int

SELECT @queue_id = queue_id FROM sys.dm_broker_activated_tasks
WHERE spid = @@SPID

SELECT @QueueName = [name] FROM sys.service_queues
WHERE object_id = @queue_id




To use this DMV user need VIEW SERVER STATE grant but this is an user DB and I can't give this grant to user.




How I can do it?



Somebody can help me?





Thanks in advance

Luca




View 3 Replies View Related

DB Engine :: GRANT Read-only Permission After A Restore

Jun 19, 2015

I'm trying to grant a read-only permission in sql 2014 after a restore and I wanted to find out what it is in SQL 20114?

View 4 Replies View Related

User Without Login : Grant To View Server State Problem

Jan 21, 2008

Hi to everybody,

I have a permission problems with user create for service.

I created an user without login in a database. I use this user for service broker activation procedure.So, this user have a certificate to exports in other database.

To generalize activation procedure of many service broker queue I try to use this piece of code :



DECLARE @QueueName nvarchar(MAX)
DECLARE @queue_id int

SELECT @queue_id = queue_id FROM sys.dm_broker_activated_tasks
WHERE spid = @@SPID

SELECT @QueueName = [name] FROM sys.service_queues
WHERE object_id = @queue_id




To use this DMV user need VIEW SERVER STATE grant but this is an user DB and I can't give this grant to user.




How can I do it?



Somebody can help me?





Thanks in advance

Luca

View 5 Replies View Related

Question About Create Database,login,user,schema And Grant Permissions.

Apr 25, 2007

Hi,



I created a database,login,user and schema like belows.









-- 2. create database
CREATE DATABASE MyTempDatabase;



-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;

--
USE MyTempDatabase;



-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;



-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;









The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.



How can I grant permissions to the user?(or schema?)

I failed to grant by T-SQL query.



Additionally, what is purppose of the ROLE? Should I create or use it?



I'm confusing in security concept(login,user,schema,role).



Thanks.

View 3 Replies View Related

Grant Backup Permissions At Server Level ?

Sep 5, 2006

Hi There

The backup database permission seems to only be at a database level , grant backup database and db_backupoperator role only exist at a database level.

However i want to permission to a user to backup any database, and he must not be a sysadmin. I cannot find a server level role that can backup databases.

How can i do this? There are alot of database so granting the permission for every database seems a waste of time, also one would have to remember every time a new database is created to grant this permission.

There must be a way to do this at a server level?

Thanx

View 1 Replies View Related

Transact SQL :: Backup / Restore Tools Which Can Restore Multiple Environments

Jun 25, 2015

I am looking for a SQL Backup/Restore tools which can restore multiple environments.  Here is high level requirements.

1.  We have 4 DBs, range from 1 TB - 1.5 TB Each Database.  When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2.  I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.

I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup.  I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible.  We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.

View 4 Replies View Related

SQL Server Admin 2014 :: Restore DB With Full Backup And Transactional Log Backup

Aug 3, 2015

Need to restore database,here's the scenario:

Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.

View 8 Replies View Related

GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] Prevent Grant From Being Automaticly Add To Each Column

Oct 15, 2007



GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] prevent grant from being automaticly add to each column?

Is there a way when you issue a grant select to a table or a view to not also grant select for each column.

The problem is when you use the grant command it automaticly adds the grant command to each column. I want to grant the permission at the table level so when the table is scripted it only has a single grant command instead of a grant for the table and a grant for each column which is not needed.

The sql managemnt studion interface will allow you to do this but onlt by using the interface. If you issue the above command from a query window it also creates A GRANT FOR EVERY COLUMN. How can I stop this behavior.

View 9 Replies View Related

How To Restore A Database From Backup With A Splitted Backup File

Apr 1, 2008

I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.

How is the syntax of the restore filelistonly and the restore database ... ?

I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....

This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.

Thanks in advance for any help.

View 3 Replies View Related

How To Restore Database From Full Backup And Several Diff Backup

Oct 17, 2006

I have a full backup and several diff backup,now i want to restore

firstly,I restore full backup

RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO

it's working,then i don;'t know how to continue

Thanks in advance

View 3 Replies View Related

Grant Execute On Sproc Doesn't Grant Privilege To Update?

Oct 31, 2014

I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?

sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@UserID int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @UserID

execute SQL:
EXEC schemaname.SetUserCulture @UserID = 9, @Culture = N'x'

error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.

View 8 Replies View Related

How To Grant Create View Access Without Grant Alter On Schema::

Aug 16, 2007

In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.

Is there any way to grant this privilage without granting alter on schema also?

View 1 Replies View Related

Finding Logins That Have GRANT CONTROL And GRANT VIEW DEFINITION

Jul 21, 2015

Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?

GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda

View 5 Replies View Related

Is It Possible To Restore From A Database Backup Without A Transaction Log Backup?

Oct 14, 2007

I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).

Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.

Thanks!


SQL Server 2005 on Windows 2003 Server x64.

View 16 Replies View Related

SQLServer Restore (HELP PLEASE)

Feb 20, 2004

Hello Guys

We´re in a big deep problem...

We lost some data from our SQLServer and now we discover that the SQLServer agent was not running and the backup was not being performed.

Is that any way to recover the lost data from the log files, even if we have to retype everything?

Regards
Andre

View 6 Replies View Related

Restore Data From .Mdf And .Ldf For SQLserver 7.0

Dec 23, 1999

Hi all,

Since my daily backup failed, I need to restore a database
from .mdf and .ldf files so that i can get all the transactions.


Thanks for your help

Sanjeev Kumar

View 1 Replies View Related

Sqlserver Login And User

Mar 30, 2006

What is the diffrence between Sql Server Login and Sql server DatabaseUser?I want to put a funtionality in my application from where administartormake Application User.these application user will be made also in sqlserver..Now I am confuse here In sqlserver Login and UserWell I will make both for A new user of my application Sqlserver Loginand Sql server user,Plz help me What to do with It..

View 2 Replies View Related

Why SQLserver Uses NTlogin Though Select To Use The Sql Login??

Oct 2, 2001

I have sql server 2000 loaded on my nt machine. Also have another sql2000 server on the network. I have connected to the these servers thru sql enterprise managers.
We have a vb application which uses sql database. I have created a DSN to the server on the network with the options to use sql login and supplied the username and passwords.

when I try to start the vb application it asks for the which dsn to use. then it gives the sql error message saying login failed for the nt user ( i.e my nt login name) then displays another login box with use trusted connection option checked. I wasn't ables to figure out why the DSN is using the nt login instead of sql login.
Any help is greatly apppreciated. Thank you.

View 4 Replies View Related

Restore (migrate) Database From MS SQLserver 2005

Nov 1, 2007

can we restore (migrate) database from MS SQLserver 2005 to MS SQLserver 2000

Pls reply.

View 2 Replies View Related

Restore Readonly Filegroup Sqlserver 2000

Sep 26, 2007



Hi,

I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:


Take a full backup of TestDatabase

Restore full backup as TestDatabaseDemo

Run long-running data-operations on TestDatabaseDemo

Take a backup of TestDatabaseDemo

Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.
Suppose we arrived at step 5, I am executing the following commands:




Code Snippet
--make full backup of TestDatabase
backup database TestDatabase to DISK='G: emp estdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G: emp estdatabasedemo.bak' with init

backup log TestDatabase to DISK='g: emp estdatabase.log'

--restore secondary filegroup from TestDatabasedemo-backup
restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G: emp estdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G: empTestDatabase_Data_secondary.ndf', NORECOVERY
--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G: emp estdatabase.bak'
with move 'TestDatabase_Data' to 'G: empTestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin
restore log TestDatabase FROM DISK='g: emp estdatabase.log' with recovery




I get the following error:
The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.

When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement
I get error:
The database cannot be recovered because the files have been restored to inconsistent points in time.


How i can restore the read-only filegroup correctly?

View 3 Replies View Related

MSDE SQLSERVER 2000: Login Failed

Feb 12, 2008

Hello

I have a installation of MSDE MSQLSERVER on my machine, in this server i run a local database. The problem began when I uninstalled the msde sqlserver and reinstalled it. The program tha uses the database not are connecting, they prompt: "login failed"

Can you help me?

View 1 Replies View Related

Restore Of DB And 'sa ' Login

Mar 12, 2002

Hi all,

I have restored a database from one SQL server 2000 installation to the target (test) server of exactly the same type.

After the restore, I have found that the sa User in my database is missing. If I go to logins under 'Security' in Enterprise Manager, the sa login is there and after viewing properties for this Login, it is checked as having acces to my offending database under the Database Access Tab.

I try to uncheck it but it tells me that the name dbo was not found in the users collection. I go back to the database and try and add the user (login = sa, Name = dbo) and returns with the error

Error 15405: Cannot use the reserved user or role name 'sa'.

Can anyone explain this and how I can reinstate the sa id (The is did not have a password on either servers.

Thanks


David Ray

View 1 Replies View Related

Restore Database From Evaluation Version Sqlserver 2005

Apr 3, 2008

Hi

I have an sqlserver 2005 Enterprise edition (Evaluation version) installed on one of my servers.It got expired after 180days and now i want to transfer the database that was residing on that Evaluation version sqlserver to a new Development server.

Do we have any way to restore the database from a already expired Sqlserver trail version to a new Development server.
I dont want make an upgrade.
Please suggest me.

Regards
Arvind

View 5 Replies View Related

Poking A Hole In Vista Firewall For Sqlserver Login

Jan 5, 2007

I've tried letting in 1433,1434 443,444 and I still can't get through.  When I turn off the firewall, it works.  What Am I missing?

View 3 Replies View Related

Property Login Was Not Set. (Microsoft.SqlServer.Express.ConnectionInfo)

Feb 3, 2008

Hi, having a bit of a problem - I'm trying to attach a database in MS SQL Server Management Studio Express. When I try in Windows Authentication I can't see any of my databases when I try to browse, so I had a look around on Google a saw mentioned that I should maybe use SQL Server Authentication. Only I don't know my user name and password, where can I configure this? I remember when I was setting up entering a password, but not a user name. Any ideas? thanksmander 

View 2 Replies View Related

Need A SqlServer Stored Procedur To Check Login Credentials

Mar 10, 2006

Sid writes "Hi,
can anybody plz provide me the stored procedure for checking the credentials(uid,Pwd) of a login page in sqlserver.

Thanks,
Sid."

View 2 Replies View Related

Login Failure After Restore

Jun 26, 2000

hi,
My requirement is as follows:

1. I Dump my Database into a dump device
2. I Rebuild my mater Database
3. I Restore my User Database.
4. At the end of the above exercise I find my relationship between sysusers (User Database) and syslogins (master) is totally screwed up. This has been causing logon problems to my User Database…
5. I tried using sp_change_users_login …but it does not seem to work.

Thanking you in advance for your help rendered

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved