SQL 2012 :: Migrating Database To New Server - Map Login To Users
Mar 10, 2014
I am migrating a database TESTDB from SQL 2008R2 to a new server running SQL Server 2012.
Management has decided the current sql users should have "better" user names. So the login and username "BadUsername" on the old server should be called "GoodUserName". Goodusername should have the same permissions as Badusername.
I have now restored a backup from the old server to the new server.
I used the following script for creating the login:
CREATE LOGIN [GoodUserName] WITH PASSWORD=N'difficultpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Then I run the following script:
ALTER USER Badusername WITH LOGIN = GoodUserName, NAME = GoodUserName
But the results are not what I wanted. I now have two database users: Badusername and GoodUserName. I would have preferred if BadUserName was "replaced" by GoodUserName, but it won't be a problem if I have to delete badusername manually. Worser is that GoodUserName have NOT "inherited" any permissions from Badusername.
Is there an easy way to transfer permissions or do I need to loop through the permissions of badusername and apply those to badusername?
View 4 Replies
ADVERTISEMENT
Feb 21, 2014
We build up a new website in .net mvc 4.0 on a w2012 server with MSSQL 2012 database.We use windows autentification and normal anonymous access through NT AUTHORITYIUSR who is also a login in the database.But we make extra login possibility for users who are registered and they can insert and modify data in some tables in the database. And I'am afraid of giving NT AUTHORITYIUSR insert and update for some column in tables.I think of set up a new login for the database. Run a new connection string on login and set up special permission for that extra user on the database, and let the user become a member of the ordinary user on the database.
View 0 Replies
View Related
May 29, 2015
I just wanted to know what the easiest way would be to migrate a SQL Server 2012 database to Oracle?
Apart from the tables and views, the database also uses a number of stored procedures and functions which will also need to be migrated over onto Oracle.
I guess the problem with generating scripts for the database (right clicking database > tasks > generate scripts) is that the TSQL scripts will not run in Oracle which uses PSQL? Is this correct?
I have also considered using the import/export wizard. Do I need to install a feature to use this to export to Oracle?
View 2 Replies
View Related
Dec 12, 2007
Hello Everyone
How do you generate a script for all the current sql server logins and generate a script for database users for each database.? You can script operaoers, tables, databases, and a lot of other objects by using the “All Tasks” shortcut menu option, but I haven’t figured out to script logins and database users. Any help would be greatly appreciated.
I’m using sql server 2000 and Enterprise Manager.
Thanks
GEM
View 2 Replies
View Related
Aug 31, 2007
Hope somebody can help a novice, so I can impress my boss.
I've got to move a database to a new server and I'm trying to use Gregory A Larsen's sp_help_revlogin script http://www.databasejournal.com/features/mssql/article.php/2228611.
It works perfectly for all users, but I want to limit it to one database by using his suggestion shown on that page:
SELECT sid, name, xstatus, password FROM master..sysxlogins
Your_db..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> 'sa'
However, when I make the changes, so it looks like this:
SELECT sid, name, xstatus, password FROM master..sysxlogins a join
int_test..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> 'sa'
I get this error when I try to run it:
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'sid'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'name'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'password'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'name'.
Can anyone see what I'm doing wrong.
The database that I want to limit the script/procedure to is called int_test on the same server, and I've checked that the table sysusers does exist, and that it has a column called name.
Any help would be greatly appreciated.
Paul
View 3 Replies
View Related
Jun 5, 2015
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
View 9 Replies
View Related
Apr 29, 2015
What I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
View 1 Replies
View Related
Apr 3, 2008
I am in a situation where I would like to use a SQL login instead of adding individuals windows login to the server. Is there a way to force a login instead of having the report server not give rights at all??
I hope that makes sense...
Thanks
View 3 Replies
View Related
Jun 9, 2015
we have an application which lets users connect to production database with windows credentials, They are able access the sql tables too with windows login. I want to restrict them from accessing the sql tables. How do I do that.?I tried a db_deny but that prevented them from accessing the application too.
View 10 Replies
View Related
Feb 2, 2007
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.
View 1 Replies
View Related
Jan 21, 2003
We are currently in the process of migrating users from a NT 4.0 domain to a win2k Domain. On some of our SQL Servers the Windows Authenticated users own objects within the database. These Windows Authenticated users also own SQL Server Job and DTS Packages. Once these Windows Authenticated users are moved over to the Windows 2000 Domain they have to qualify there database objects, they can not see their SQL Server Jobs they created and they cannot modify the DTS Packages they previously created. Is their a tool or script out there that can fix this problem of moving the Windows Authenticated users smoothly over to the new domain.
View 3 Replies
View Related
May 15, 2014
Where is located the hash password for the contained database users?I have a script that prints all creating statement so that a Dev environment security can be reapply after a prod data refresh but I can't find the table containing the hash password when the user is "with login" for contained database.
View 4 Replies
View Related
Jun 3, 2014
I have been using the software, and it has been working fine (on windows user A). Now, I have created another windows user (User B), and would like to use the same software/database. The software launches fine (User B), but cannot access the created SQL database (created with user A)
How do I setup the database to allow access from all users on the same PC?
View 2 Replies
View Related
Nov 26, 2014
I am trying to clean up security. When I check tables in a specific database I see a list of users with select access. There are 1000+ tables in the database. I know I can do 'revoke select on table_name to user_name' ....
View 3 Replies
View Related
Apr 16, 2008
Hi all,
Have just tried my sql server 2005 connected asp classic website on II7 for the first time and have gotten some errors.
First off I have unchecked show friendly errors in IE and have enabled ASP to send error messages to the browser.
My error I get is when I execute a basic .asp page:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "mydatabasename" requested by the login. The login failed.
/index.asp, line 10 which is dbConnect.Open cstring
from this peice of code:
sub dbOpen()
set dbConnect = Server.CreateObject("ADODB.Connection")
cstring = "DSN=mysqldsn; Trusted Connection=yes"
dbConnect.Open cstring
end sub
I have gone into ODBC and have setup both a user dsn and file dsn that equals mysqldsn and points to my database (I know you only need to set up one DSN but I'm not sure which)
I also notice under mywebsite home panel there is a connection string option. Do I do anything with that?
Definatley a lot more to it than XP's II6!
Any help or advice would be greatly appreciated.
View 3 Replies
View Related
Jan 24, 2015
I have created a new login in principle server and provided dbowner permission to principle db.
How do I transfer this login to mirror server and assign the same permission to mirror dd?
View 5 Replies
View Related
Feb 21, 2008
After migrating from Win 2000 to Win 2003 and SQL 2003 to 2005, there is 1 report that will not open due to this somewhat common error. The username AMC is one we do not know the password for. It was not setup in Active Directory, only in SQL. The migration was a simple backup from the old sql and restore to the new one of the databases which I believe got the username as well. I am very new to this so any information would be helpful. I have researched the error for 3 day now with no workable answers. I did copy over the ODBC System DSN information as well as the IIS Virtual Directory that was needed. When entering the report in question, the first page which is a calendar opens up fine, it is when I choose dates and enter to view more information that this error comes up.
I will also post this in another forum in case it doesn't belong in security.
Any help would sure save my bacon.
Server Error in '/Intranet/Reports' Application.
Login failed for user 'amc'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Login failed for user 'amc'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[OleDbException (0x80040e4d): Login failed for user 'amc'.]
DataDynamics.ActiveReports.Web.-e.-ea(String receipt) +381
DataDynamics.ActiveReports.Web.WebViewer.-11() +55
DataDynamics.ActiveReports.Web.WebViewer.-1f(HtmlTextWriter output) +57
DataDynamics.ActiveReports.Web.WebViewer.RenderChildren(HtmlTextWriter output) +766
System.Web.UI.WebControls.WebControl.RenderContents(HtmlTextWriter writer) +7
System.Web.UI.WebControls.WebControl.Render(HtmlTextWriter writer) +29
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +130
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +24
System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer) +59
System.Web.UI.HtmlControls.HtmlForm.Render(HtmlTextWriter output) +68
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.HtmlControls.HtmlForm.RenderControl(HtmlTextWriter writer) +37
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +130
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +24
System.Web.UI.Page.Render(HtmlTextWriter writer) +26
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1896
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 9 Replies
View Related
Mar 19, 2002
I am migrating MS SQL Server 6.5 databases to a new server and wanted to create the data & log devices differently than they are on the old box. My dbcc checks are giving me 2558 errors because my devices weren't built the same size in the same order. I thought if I didn't copy the master, I didn't have to rebuild the database exactly the same. Can anyone tell me if there is a way to rebuild different device sizes and do a successful restore ? Thanks in advance !
View 1 Replies
View Related
Apr 10, 2003
I need to move all existing dbs to a new server. One of the database is over 90GB in size. If I plan to use to restore database option, should I restore all user database first, then the system databases(master, msdb) or should I restore the system database first and the user databases second??
Thanks.
View 11 Replies
View Related
Sep 18, 1998
Hi!
I got a project of moving a web site of one a firm from one ISP machine to another ISP
machine. They are using Cold Fusion with SQL Server as the database. I am new to SQL Server.
I want some tips on how to migrate the database from one machine to another.
Any little help will be appreciated.
I will be waiting for suggestions.
Mark.
View 1 Replies
View Related
Mar 28, 2001
Hi,
We are planning to migrate data from access to sqlserver. I have good knowledge on sql server...but never used access to migrate. Can anyone help me with the basics to be kept in mind and methods and errors you get in migrateing data.If possible explain in detail and what is to be done while migrating data.....thank you in advance.And its urgent please.
View 3 Replies
View Related
Sep 17, 2001
i am migrating data and schema from oracle server to sql server., when i try to do the dts it gives me an error saying could not resolve tnsnames.ora. iam not sure what is the problem.
My oracle server host is dbserver.hari.com.edu
and its name is Prod (name of the machine)
i tried both it still gives me the same error., but when i install an oracle client on the machine and configure the same it is working great.
My question is, is it necessary to install oracle client to migrate database from oracle to sql server. If not how do i do the same...what is the process to do it. since this is a prodction machine we don't want more of applications running on the same machine.
Your help is appreciated, thanks in advance.
Kavitha
View 1 Replies
View Related
Mar 25, 2008
Hi all,
I'm not sure if this is the right place to seek help. I'm trying to migrate a small sql 7 database to a new server runing sql 2005 server express. is that possible? if anyone has any instructions or point me to the information that i need to get this done. i'm not a dba so any details instructions would be really appreciated. Thanks in advance.
Brandon
View 1 Replies
View Related
Sep 20, 2006
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
How do I solve this problem?
View 9 Replies
View Related
May 31, 2014
I have a db to manage the creation of invoice number designed for a web application.
My problem is how to manage the concurrency when the users need to create an invoice number.
View 9 Replies
View Related
Sep 5, 2006
I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.
How to migrate it?
View 4 Replies
View Related
Mar 6, 2008
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies
View Related
Oct 27, 2004
Hi,
Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword")
Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn)
Line 15: objConn.Open()
Line 16: objRdr = objCmd.ExecuteReader()
Line 17: While objRdr.Read()
Source File: C:finalyearproject2sample.aspx Line: 15
Please Help!! Im a beginner to this, so if anyone knows the answer, take baby steps when explaining. Thanks
View 3 Replies
View Related
Jul 27, 2005
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
View 9 Replies
View Related
Dec 19, 2003
I am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
View 5 Replies
View Related
Aug 25, 2015
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">Friends,</g>
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">I</g>
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g>Â store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
<g class="gr_ gr_418 gr-alert gr_gramm Grammar multiReplace" data-gr-id="418" id="418">please</g>
best way to migrate logins only related to the specific database to the new server.Â
View 4 Replies
View Related
May 17, 2015
As I am have query, I want to find sqlserver 2012 user/login last login date, when user last time date logon to server.Â
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>
View 6 Replies
View Related
Jul 24, 2002
SQL Server 7 or 2000.
I restore a database on a different server from a device file. This database has a user defined for it. In this case, 'privuser' is the name. No login has been defined.
I can add the privuser login but cannot grant db access because privuser already exists in the db. I cannot drop 'privuser' because the user owns all the user objects in the db.
How can I make the privuser login I create access the db using the privuser username in the db? Would having the login created before restoring the db as a new db work?
View 4 Replies
View Related