Reseting Users After Restoring SQL Server 2005
Jan 9, 2008
I am restoring a production copy of db XYZ to the development server db XYZ. I want to retain the original users on the development server for db XYZ. How do I create a script that will reset the users on the development server with the same permissions and access they had before the production copy was restored on development? I have searched and searched and cannot find the answer. I can use a third party tool - Embarcadero - to script out the create and update stmts but it is not 100% correct. I end up having to do alot of manual work.
any assistance would be greatly appreciated.
thanks
View 5 Replies
ADVERTISEMENT
Apr 12, 2000
I backed up and restored a database successfully except for the users. The only user that shows is dbo, but I can't add a user with the proper user name from my old server; I get the error that the user or role already exists in the current database.
When I go to role, name of public, there is the user I need to add. I can't remove it as it's a table owner, but I need to add it to the list of users, so I need to remove it then re-add it.
I tried using EXEC sp_changeobjectowner to no avail.
Thanks for any help/guidance.
View 2 Replies
View Related
Apr 11, 2007
Hi,
I have a few db's that I am backing up from an old sql2000 system and moving them to the new sql2005 server. The first db's restored without any problems but now I am getting some problems with users and logins.
I am restoring in the following order
1.) Creating empty db "123"
2.) Restoring database from file to database "123"
3.) Creating Login to this database (error happens when linking it to the database, "user exists")
When connecting thru QA I get "Cannot open user default database login failed"
I only have 1 login per database and I don't mind manually deleting and recreating them all but it doesnt seem to be working :S
Any suggestions much appreciated!! :)
Thanks!
mike123
View 4 Replies
View Related
Jul 20, 2005
Hi,I restored a database 2 months ago. After the restore, I lost all my"users" for that database. It has been a while, so I don't rememberthe detail of what I did.How can I reserve all the users when restoring a database from a file?Did I do something wrong?Thank you in advance,Eddy
View 1 Replies
View Related
Jun 28, 2000
I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?
Thanks.
View 2 Replies
View Related
Mar 6, 2007
After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help
View 1 Replies
View Related
Mar 24, 2007
Hello,If I backup and restore an express database to sql server 2005 standard, will there still be limitations in regards to the database size, cpu...etc.? Thanks,Jon
View 1 Replies
View Related
Dec 21, 2006
Hello,
I received a backup Sql Server file and I am trying to restore it. When trying to restore the backup using SQL Server 2000, I get the following message:
"the backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. RESTORE DATABASE is terminating abnormally."
After doing some research online, I figured out that the backup I received must be from SQL Server 2005. Therefore, I installed SQL SErver 2005 on my laptop (so now I have both 2000 and 2005 on my laptop)..
I tried to use the restore database option in SQL Server 2005 ( SQL Server Management Studio) to restore my backup, and I get the same error message (written again below) as I did when using SQL Server 2005:
"The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. RESTORE DATABASE is terminating abnormally"
Can anyone please provide me assistance on what I'm doing wrong? Is the issue that I have both SQL Server 2000 and SQL Server 2005 installed on my laptop? Thanks.
View 7 Replies
View Related
Apr 3, 2007
Hello Everyone,
I am trying to restore a bak file which came with code for a tutorial. I think it is safe to restore, but sql server 2005 express has the following problem when I try to restore it.
TITLE: Microsoft SQL Server Management Studio Express------------------------------
Restore failed for Server 'BOBSQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDashboard.mdf'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
The links given for help have no information about the problem and I'm not really sure where I should be looking to solve this. I'm a newbie to databases so if you could provide a step-by-step answer that would be best. Thanks in advance.
Robert
View 4 Replies
View Related
Mar 12, 2007
I would like to know if it is possible to make a backup of a database in a SQL Server 2005 server and restoring it in a SQL Server 2000 server.
Thanks a lot in advanced.
View 5 Replies
View Related
Oct 3, 2007
Hi,
I have a sql server 2000 backup and I want to restore it into a Sql Server 2005 database.
I did the restore on the usual way. All the database tables were created, but the data didn't get restored.
Pls help!
Thanks!
View 7 Replies
View Related
Jul 31, 2006
SQL 2005 Express - Database Restore size problem
Was this post helpful ?
Hi,
I'm trying to restore a SQL Server DB Backup from a SQL Server DB Server on to my Laptop (SQL 2005 Express)
When I execute a restore filelistonly command on the backup file, It seems that the Database included is 1GB, but the Log file is 91 GB in size, which exceeds my diskspace.
I can restore the Data on its own without the log file, but the the Database stays in "restoring" mode. I've tried to switch the restore flag off (update sys.databases set state = 0 where name = 'G001'), but I can`t seem to be able to do it, even if I try to allow updates via:
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Any ideas how I can restore the database without restoring the enormous logfile?
Thanks in advance...
View 3 Replies
View Related
Feb 19, 2007
Hi!
I'm a beginner w ith SQL Express and am having some problems. I am using 2005 SP1 and installed the SQL Server Management Studio Express as well. I have a backup from a SQL Server 7.0 database that I am trying to restore into my database using the management studio. When I try to restore from the file into my database, I get the error,
System.Data.SqlClient.SqlError: The backup of the system database on the device D:GMBeta2Local.bak cannot be restored because it was created by a different version of the server (7.00.1063) than this server (9.00.3033). (Microsoft.SqlServer.Express.Smo)
Is this true? Is there any way for me to get this data into my SQL Server 2005 express on my laptop?
View 1 Replies
View Related
Jul 26, 2006
Dear All,
I am experiencing a problem that, after i have made a backup database file in SQL Server version (9.00.1090), when i restore the database in a newer SQL Server 2005 version(9.00.1399), an error occurs.
The error is:
Restore failed for Server 'demo'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The database was backed up on a server running version 9.00.1090. That version is incompatible with this server, which is running version 9.00.1399. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)
Anyone can tell me how i could fix this?
Why would it happen?
Thank you very much
Regards,
George
View 1 Replies
View Related
Aug 22, 2007
I'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
View 2 Replies
View Related
Nov 11, 2006
Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)
Thanks, radco
View 1 Replies
View Related
Jul 26, 2007
I'm building a web site. there is a database.
I've set a primary key and unique of a field.also I set it to auto numbering.
Everytime I insert a record , that field will increase 1 (type bigint , start from 1).
After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?
View 1 Replies
View Related
Aug 27, 2007
I've built a development server for my SQL 2005 productionenvironment. I've restore the databases from a backup, and I want tocopy the users from my production to development machine.How can I do this?TIARob
View 2 Replies
View Related
Nov 21, 2007
Hey guys
We currenlty have SQL Server 2000 installed inhouse for testing. A client has given us a backup of their 2005 database and i'm wondering if the restore will work into 2000, or any issues i should be aware of. Should i just install msde locally on my machine and restore the database?
thanks
View 2 Replies
View Related
Feb 28, 2007
In SQL Server 2000 we had a view that would show the user credentials and the password hash. The reason we need this is that we use SQL Server authentication on the database. To test users, we have a login with little access, and it should be able to see the view and compare the password supplied against what is in the database, and then let the code handle a graceful exit if the password is invalid. I am trying to do this with SQL Server 2005, and I am running into trouble. I am trying to do this with a function, since there I can set the EXECUTE AS clause (in theory) and leverage the privlidges of a specific user in the database. Here is an example function:
CREATE FUNCTION check_acct.fn_allusers (@test int)
RETURNS @users table (username varchar(50), passwd varbinary(256))
WITH EXECUTE AS caller
AS
BEGIN
INSERT @users
select name, password_hash from sys.sql_logins
RETURN
END
GO
GRANT SELECT ON fn_allusers TO user_acct
I cannot get to all rows in the sys.sql_logins table unless I first:
GRANT VIEW ANY DEFINITION TO CHECK_ACCT
then when I call this function from CHECK_ACCT, I am able to see the data. If I change the WITH EXECUTE AS 'CHECK_ACCT' and execute thsi function from USER_ACCT, I do not get the same results. Do functions not inherit VIEW DEFINITION proivlidges? Any suggestions on how to do this but limit access to these secure objects to this one function?
View 4 Replies
View Related
Sep 13, 2004
Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.
What can I do so that users are able to select from these tables after they are created?
Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?
All help is appreciated.
View 2 Replies
View Related
Jan 16, 2008
Hi, i just need some help?? just some info?is it TRUE that SQL Server 2005 Express Edition (free) is capable ONLY of SINGLE user?i mean if i have a data driven page, if someone is using or editing the data in my page, does no one can access my data base simultaneously??i hope you could enlighten my mind, thanks
View 2 Replies
View Related
May 7, 2008
Does SQL Server 2005 Workgroup Edition have a limit to the number of user logins I can make?
View 1 Replies
View Related
Mar 26, 2008
I am sure that I am missing something which is why I am in need of the communities assistance...
I have installed SQL Server 2005 Express Edition. I can log in with the Windows Authenication and the SQL Server Authentication using the 'sa' account. This is all good...
What I want to be able to do is add 1 to 3 more users so they can gain access to the database I have created to run queries and add data. This is the part I am confused about. How do I get additional users added??
Thanks in advance for your time and assistance!
View 3 Replies
View Related
Oct 16, 2006
I need to set up 1 new user in SQL Server 2005 to be able to read specific tables in a db (db1).
The user will connect from MS access using odbc links (SQL Native client ot SQL Server driver)
I've tried to set up one and once logged on from the user workstation, I can only see sys. tables and INFORMATION_SCHEMA tables.
None of the required db1 tables appear.
under Security/Logins I've created User1:
SQL Server auth. with password
default db = db1
server_roles = none
user mapping = map, db1, user1,dbo
securables = none
status = grant, enabled
on the access db, the odbc link was set up with default db = db1
Why can't I see any of the db1 tables?
How can I restrict access to the sys. tables?
Thank you
View 3 Replies
View Related
Nov 21, 2006
We are running SQL Server 2005 in a Windows 2003 domain and I have a situation where some of my users are unable to connect to the SQL Server unless they are a member of the sysadmin group. Any attempts by these users to login result in a login failed,
Error: 18456, Severity: 14, State: 11
Which indicates that it is a valid user who does not have access to this SQL Server.
I have been able to narrow the failures down to the following situation:
Create a user, TestUser1, as a member of 1 domain local group TestGroup1
Give TestGroup1 access to SQL (standard public access to master)
All good. Login succeeds.
Add TestUser1 to another domain local group TestGroup2
Attempt to login to SQL Server -> login failed.
Add the user explicitly -> login failed
Add one of the groups to sysadmin -> login succeeds
It seems that as long as the user is a member of more than one AD group, and none of those groups is a member of the sysadmin server role then the user is unable to login. Obviously having all of the users as sysadmin isn't a workable solution, has anyone seen this issue before?
I have been able to replicate a similar situation in our test domain, but in that case the issue is resolved by adding the users explicitly to SQL Server (still not an ideal solution).
Interestingly, if I run the same test in our test domain but use global groups, it works. But unfortunately the network admin tells me the groups must stay as local.
Any help would be greatly appreciated.
Regards,
Daniel Watkins
View 13 Replies
View Related
Jul 6, 2007
Hi All,
I have just migrated couple of test databases
from SQL Server 2000 to 2005 (side by side).
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
Thanks for your time..
Bidyut
View 5 Replies
View Related
Sep 27, 2006
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
View 13 Replies
View Related
May 16, 2008
I have been struggling with this for a while and cannot get it right. I have read countless articles on the internet as well as MSDN documentation about SQL Server 2005 and no success so far.
What I want is a database with multiple SCHEMAS, DATABASE ROLES and USERS in order to have a finer grade of security to access the various objects.
My schemas are (in order to simplify the situation and provide an example of the setup):
- [dbo] the standard DBO schema which is always default. Contains public objects.
- [com] a special module integrated into the system
- [ofc] contains objects used by back-office only
- [aud] contains objects used for auditing, etc.
Each of these schemas has their fair share of tables, views, functions and stored procedures which have been created appropriately (i.e. CREATE TABLE [ofc].[Addresses]), in other words prefixed by the name of the schema to which they belong.
Then I have created various database roles (don't confuse them with SQL2005 application roles) as follows:
- PublicRole mostly used for viewing, no data alterations
- WorkerRole used by front end processes that need write access to data in [dbo] and [aud]
- OfficeRole used by back-office for dealing with backoffice data (basically [ofc], [aud] stuff)
- AdminRole used by application administrator
Each of these database roles has been defined with owner 'dbo', none of them own any schemas. And last but not least to each of these roles I have selected the above named schemas (dbo, ofc, aud) as securables and for each of those securables schemas I have then given the correct set of GRANT/DENY on the Alter, Control, Delete, Execute, Insert, References, Select, Update, Take ownership and View definition.
As an example, the PublicRole role has been given the following permissions (Y=GRANT, N=DENY):
Table #1 of Application Permissions [dbo] [com] [ofc] [aud]Alter N N N N
Control N N N N Delete N N N N Execute Y Y N Y Insert N N N Y References Y Y N Y Select Y Y N Y Update N N N Y Take ownership N N N N View definition N N N N
And I have the following minimum set of database users defined (the server login has the same name):
- upublic, assigned to PublicRole
- uworker, assigned to WorkerRole
- uoffice, assigned to OfficeRole
- uadmin, assigned to AdminRole
As I understand when I assign these users to a particular custom Database Role, the users inherit the permissions granted to THAT role thus liberating me from having to assign the permissions to each and every user account on the same role.
What I expected was that when I logged in to the database with the upublic user account I would at least be able to view (SELECT at least) ALL the objects with the exception of those in th e[ofc] schema. Unfortunately the account is not able to access ANYTHING at all, I get an error like
"The SELECT permission has been denied on object XYZ, database DB, schema 'dbo'"
So, when I use Management Studio to look at the properties I selected the PublicRole and it showed the upublic user as a member of the role (Members of this role). So far so good.
Then when I switch to the Securables page for this role it shows all the schemas I defined and for each of them the same list shown in Table #1 except it has two lines for each permission, the first for Grantor dbo and the second for Grantor upublic. The first has the same permissions I assigned to the role (as shown on Table #1) but the 2nd does not show a checkmark on either GRANT or DENY!!! For example ([x] = checked, [ ] unchecked):
Table #2 Explicit permissions for Securable
Permission Grantor Grant Grant-with Deny
Select dbo [x] [ ] [ ] Select upublic [ ] [ ] [ ]
What am I doing wrong here? Apparently I then have to repeat the whole grant/deny for EACH and EVERY USER I define when the idea was that I would grant/deny on the database role and every member of that role would inherit those permissions automatically.
View 3 Replies
View Related
Mar 21, 2007
As I said in the subject I've a problem trying to restore a backup of a previous db created in sql 2000 server
When I try to do it I recive the following message:
____________________________________________________________________________________
System.Data.SqlClient.SqlError: Il set di backup include il backup di un database diverso dal database 'musica2007' esistente. (Microsoft.SqlServer.Express.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
------------------------------
Program Location:
in Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
in Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
in Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
____________________________________________________________________________________
What should I do? What's the probem? I've already tried to look for the solution in other messages but I didn't find anything..... Thanks for help,,, by Luke
View 1 Replies
View Related
Feb 8, 2006
Hi ,
I am trying to restore database , which is backed up from SQL 2005 ver 9.00.1399.06
to anothere machine have SQL 2005 ver 9.00.1399.00.
When I try to do a restore I am getting the following error!
Too many backup devices specified for backup or restore ; only 64 are allowed.
RESTORE HEADER ONLY is terminating abnormally . (Microsoft SQL Server, Error:3205)
Please give me some solution for this.
View 12 Replies
View Related
May 3, 2007
Howdy all. I want to be able to restore just 1 filegroup from a filegroup backup, without having to restore the log afterwords even if the data has changed post filegroup backup. While this would leave the data inconsistant, it may be desired at times. From BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
************************************************** ******
If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).
************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got deleted from a set of tables in a filegroup right after I took my last filegroup backup, so I just want to restore that filegroup back to that backup, but leave my other filegroups alone. Restoring the log brings the data back to a consistant state when the log was backed up, which may not be what I want.
Can this be accomplished?
TIA, cfr
View 2 Replies
View Related
Jun 20, 2007
Hello,
I'm having an issue with stored procedures after restoring a db from 2000 -> 2005.
If any stored procedures isnt owned by dbo (eg tomsqluser.updateItems rather than dbo.updateItems) then it just wont work and Studio Management Server doesnt give the permission to change it to dbo.
Is there any way around this after restoring?
View 10 Replies
View Related
