March 31, 2009 at 4:19 am
Hi,
I have user who is not sysadmin (the fix server roles are bulkadmin, dbcreatorprocessadmin and securityadmin). The user is able to restore a database using a script (the GUI doesn't work) but when the I try to connect (or select) to the database with the same user, I get an error that I have no permission to connect.
The script to restore the DB (which succeeded) was:
RESTORE DATABASE Tmpdb
FROM DISK = 'E:\Tmpdb.bak'
WITH MOVE 'Tmpdb27_Data' TO 'c:\Tmpdb.mdf',
MOVE 'Tmpdb27_Log' TO 'c:\Tmpdb.ldf'
Does anyone know what should be added to the script or what server fix roles should be given to the user so that I will be able to connect to the db using this user after I restore it?
(Note that this is a temp DB that is created a dropped after some checks)
Thanks,
Shai
March 31, 2009 at 5:02 am
If you're the admin on the server you should be able to connect to it. What specifically is the error message.
If you have security admin privileges on the server, you can add your login to the database as a user.
CREATE USER xxx FOR LOGIN domain\xxxx
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2009 at 5:15 am
That is the problem, I do not have "admin" permission on the server. So what I need is a way to connect to the database after I restore it.
March 31, 2009 at 5:18 am
Sorry, I read again you answer and understood what you meant.
I will try it
Thanks
March 31, 2009 at 5:22 am
As already discussed, incase if you are a sysadmin on the box, you will be in a position to grant database access to the required user...
However going forward its always better to script out the user permission script prior to restoring the database and rerun the scripts once the restore is completed.
-Rajini
March 31, 2009 at 5:41 am
But even then, you can only add users if you have the security admin privileges to do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2009 at 5:59 am
I ran the user creation script with a user with security admin and I still got an error that I don't have permissions....
March 31, 2009 at 6:09 am
So from the sound of it, you have dbcreator rights, but not securityadmin. Without at least that, you can't restore a database and give permissions to access it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2009 at 6:14 am
I have both dbcreator and securityadmin rights...
I tested your script on another database where I have dbowner rights and it worked. But that solution is weire - How can you be a dbowner if you just restored the databse?
March 31, 2009 at 6:15 am
Just to clarify - I'm working on SQL 2005 and not 2008
March 31, 2009 at 3:25 pm
Permissions required for performing restore -
Server role : sysadmin, dbcreator
DB role : db_owner
http://technet.microsoft.com/en-us/library/cc966495.aspx
MJ
April 1, 2009 at 12:49 am
Restore was not the problem...
The problem was that I was not able to connect to the database I resotred after I restored it.
April 1, 2009 at 1:04 am
hi,
After restoring the Dbs normally the user loses its logins/permissions when restored over a different DB. Please try executing the stored proc
use master
go
sp_change_users_login 'Auto_fix','loginname'
Have a nice day !!!
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
April 1, 2009 at 3:41 am
Doesn't work...
The script runs but the user is till unable to connect to the database
April 1, 2009 at 12:22 pm
Execute the code mentioned below and paste the output here:
use DBName
go
sp_change_users_login 'report'
MJ
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply