Script For User Mapping
Sep 24, 2007
Hi all,
I have to provide user mapping between two databases on the same server so that the tables and content of both the databases can be accessed by each other.
I know how to do it using Enterprise Manager, but I have to do it via script as in my asp.net application the databases are created dynamically.
If I am not wrong, then "sa" login will be required to execute this script, but no problems as we will be having the "sa" password also.
Can anybody please let me know how to do it exactly. Its very urgent.
Thanx a lot in advance.
Snehal Bhayani
View 2 Replies
ADVERTISEMENT
Sep 24, 2007
Hi all, I have to provide user mapping between two databases on the same server so that the tables and content of both the databases can be accessed by each other. I know how to do it using Enterprise Manager, but I have to do it via script as in my asp.net application the databases are created dynamically. If I am not wrong, then "sa" login will be required to execute this script, but no problems as we will be having the "sa" password also. The server is sql server 2005.
Can anybody please let me know how to do it exactly. Its very urgent. Thanx a lot in advance.
View 2 Replies
View Related
Apr 29, 2008
I need to get a list of users and databases that a sql login is mapped to. Is there a view or a sp that can get me this information?
View 18 Replies
View Related
May 5, 2008
In an event of a system restore from development to production, what would cause the mappings for a database user to be whiped out?
Thank you in advanced for the clarifications.
Al
View 9 Replies
View Related
Aug 22, 2006
Hello, I am developping a web application who is connected to SQLServer 2005 via the connection sring as below
<add name="LocalSqlServer" connectionString="Data Source=IBM7;Initial Catalog=Reclamation;Integrated Security=True "
providerName="System.Data.SqlClient" />
I want to know which user (bydefault ) who is connecting to the DB . and how is mapped this user with user in SQL server?
Thanks in advance
View 3 Replies
View Related
Jan 23, 2008
Hello.
I am attempting to migrate an old third party app from 2000 to 2005 but I have a problem that I hope you can help me with.
To explain,
On 2000, in sysusers of the third party app db I have a username that contains a . This user has an associated server login (that uses SQL Server authentication) of the same name but without the .
so, the database user name is APP1 and the server login is APP1
I checked the SID's between the user and the login and they are the same which would suggest they are mapped to each other , however when I run sp_helplogins the login does not show as mapping to any user. The database user also has a different status, altuid and Isaliased value than any other user in sysusers but I confess to not knowing what the significance of this might be.
As far as I was aware you can't create a database user with a name that contains this invalid character so firstly I'm wondering how it got there. When I look at the databases access tab of the properties through enterprise manager of the APP1 server login, the APP1 user is not displayed. If the SID's between the login and the user match then shouldn't the APP1 user be displayed in the database access list? or have I got the wrong end of the stick here?
Despite all that, it isn't really a problem on 2000 because the app works ok. Now, to migrate it to 2005 I restored a full back up of the 2000 database and now the problem is that when I look in sys.database_principals the APP1 user is not there after I've restored it. It still shows in sysusers however. The main problem is that I cannot map it to a login using sp_change_users_login because I get the following error:
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'SQSUPD' is absent or invalid.
If anyone can tell me why this user does not show in sys.database_principals or more importantly, give me any pointers on how to resolve thie login mapping it would be much appreciated as I am now stuck!
Thanks
View 3 Replies
View Related
Aug 20, 2015
SQL script that creates a User Mapping for NT AUTHORITYNETWORK SERVICE to a database?
View 3 Replies
View Related
Mar 19, 2008
Hi Friends,
I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.
The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.
Any thoughts!
View 5 Replies
View Related
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
Feb 18, 2007
Hi;
Can anyone please tell me how to do re-mapping? or give some resources address that I can learn easily?
Thanks.
View 5 Replies
View Related
Sep 5, 2006
hi! i have two different databases (SQL 2005 and Oracle) and i need to map their tables with one another. how will i do this?
thanks
View 6 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Jul 20, 2007
I'm using NHibernate with SQL Server Express Edition and I've been problems with the data type to be defined in the "type" attribute on the "property" tag in the XML file mapping.Has someone worked with that?I need to match the appropriate NHibernate type to the following SQL Server Types: uniqueidentifier;bit;ntext;I have been getting "type mismatch" error when the code is trying access the data. Thanks a lot!
View 2 Replies
View Related
Dec 26, 2007
hi friends..
i want to insert data in sql server2005 table through asp.net
column names of this table comes from diffrent tables.....
like Rollno--- in my new table....but in old table1 this is like RNO & from old table2 this is R_no
so i want insert this two old diffrent table data into my new table...but i have problem coz of diffrent column names of old table...
can you help me 4 that...
View 3 Replies
View Related
Aug 25, 2006
Hello,
I have a requirement to link two databases on the same server. Besides using the microsoft link option is there any other method by which we can link these two databases. Responses will be highly appreciated.
Thanks
View 3 Replies
View Related
Aug 6, 2007
I'm trying to find out the best O/R mapping tool for .NET. A perfect tool would begin with the database model and would be capable of generating 2 layers: the Domain layer (normal classes) and the Persistence layer (methods for storing and retrieving the domain layer objects from the database.
This tool would also be capable of generating the corresponding Visual Studio solution and projects.
I've already tried NetTiers, but it generates so many layers that the code is very dificult to manage and understand.
So any of you know a tool similar to the described above?
View 1 Replies
View Related
Aug 23, 2014
We have a Item table and a Price table. Structure is mentioned below. An Item need to be matched with the Price table (or vice versa is also fine) and get the Cost from the Price table. The challenge (which I feel :) ) is, mapping or Columns to be looked up between these two tables are not fixed.For an Item, Country, City and Number of Days columns can have a value or can be null. For ex, Paper, US, New York <10 days is one combination which should be matched to the Item table. For Paper, UK, NULL, <5 days is another combination which need to be checked. it is kind of dynamic look up of columns between the two tables.
Price Table
Item Country City Number of Days Cost
Paper US New York <10 100
Paper UK <5 150
Paper Chicago >10 200
Pen China <10 250
Item Table
Item Country City Number of Days
Paper US New York 5
Paper UK London 3
Paper US Chicago 15
Pen China Shangai 5
Paper China Beijing 15
Paper US Chicago 5
View 2 Replies
View Related
Apr 1, 2008
Hi Guys
I have a table with a column and value like below.
A1JAN
A1FEB
A1MAR
A1APR
A1MAY
A1JUN
A1JLY
A1AUG
A1SEP
A1OCT
A1NOV
A1DEC
A2JAN
A2FEB
A2MAR
A2APR
A2MAY
A2JUN
A2JLY
I want to map all these values to one value like AL AVG
How do i implement the solution so that all those different values map directly to one value.
Thanks.
View 5 Replies
View Related
Mar 21, 2007
I need to generate a document with the fields mapping from a DataFlow Task. Anybody knows how to do this?
Know I'm using screenshots from the destination object mapping.
Thanks
View 5 Replies
View Related
Jun 20, 2007
I read in my record. As we proceed down the pipeline, I want to put kind of record (with length 742) into one path, and a different kind of record (with length 242) into a different path. My problem is that I have (obviously) different fixed width column mappings for the two kinds of records. How can I change the column mapping mid-stream? Can I? Do I need to just write out to flat files and go from there?
Thanks!
Jim Work
View 3 Replies
View Related
Mar 14, 2007
Hi,I use SqlDataReader to read one row from database and than set some properties to values retrieved like this:string myString = myReader.GetValue(0) // this sets myString to first value in a rowIf, however, I change order of columns returned by stored procedure myString would be set to wrong value. Is there a way to do something like this: string myString = myReader.GetValue["ColumnName"];
View 7 Replies
View Related
Oct 15, 2007
I am trying to attach a db to SQL Server 2005 and assign a User to the DB that can read and write. I am using one user (CreateDBs) to attach the DB and a second user (TestUser) to do the reading and writing. I run the following code and the DB is attached but I get the following error when I run the code below.
********************ERROR********************
System.Data.SqlClient.SqlException: Cannot open database "hello2" requested by the login. The login failed.
Login failed for user 'CreateDBs'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NewSite.submit_Click(Object sender, EventArgs e) in X:InetpubRMA4NewSite.aspx.vb:line 120
********************CODE********************
'Need to attach to SQL Server
Dim objConn As New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=master")
objConn.Open()Dim objCmd As New SqlCommand("EXEC sp_attach_db @dbname = N'" & sitename & "', @filename1 = N'X:sites" & sitename & "Database" & sitename & ".mdf', @filename2 = N'X:sites" & sitename & "Database" & sitename & "_log.ldf'", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()objConn = New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)
objConn.Open()objCmd = New SqlCommand("EXEC sp_addrolemember db_datareader, TestUser", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()objConn = New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)
objConn.Open()objCmd = New SqlCommand("EXEC sp_addrolemember db_datawriter, TestUser", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()
It still doesn't work if I use sa as the user. I know I am missing a step, what is it?
View 2 Replies
View Related
Mar 13, 2008
Hi
I have 2 tables defined as follows:
Table1 = uid, Field1, Field2, Field3 ... Fieldn, FormUID
Table2 = FormUID, Label, Position
When I query Table1 I would like to replace the column name of Field1...Fieldn with the Label from Table2 where the Position = n value of Field lable
e.g. lets say Table2 contains the following
1, customerName, 1
1, customerTitle, 2
1, customerDOB, 3
and Table1 might contain
1, Paul Jones, Mr, 21/09/1987, 1
when I query Table1 I would get
uid = 1, Field1 = Paul Jones, Field2 = Mr, Field3 = 21/09/1987
what I would like to get is
uid = 1, customerName = Paul Jones, customerTitle = Mr, customerDOB = 21/09/1987
I have up to 20 Fieldn columns so need to do this for all columns even if there is no matching columns.
any help would be great
regards
View 3 Replies
View Related
Apr 1, 2004
Hi all,
I'm not sure how easy of a task this is, or how to go about it, so I thought I'd ask here. I am doing a major overhaul to my site and am re-programming the backend which involves re-formatting my tables, table names etc.
What I want to do is set up a new table in the format I want for the new site, but still be able to have the live data that is being submitted to the site now, be entered into the new table so that I don't have to shut down the site for days to transfer and reformat data. Example
CURRENT SITE
Table 1
Field1: Name
Field2: Address
NEW SITE
Table 1
Field1: Name
Field2: myAddress
Field3: City
I want to be able to somehow map the two together so that when data is entered into the Name field on the current site, that same name is also inserted into the new site table as well. Mapping like this:
(current site)Name -> (new site)Name
(current site)Address -> (new site)myAddress
The city field would be left unaffected as that data would only be entered once the new site is up and running.
I know I could do this from a stored procedure on the exiting site or by re-writting all of my SQL to insert into both tables, but right now all of the SQL is hardcoded into the pages and I have A LOT of pages. Is there a way to map the tables together from within SQL Server?
Cheers,
Jeff
View 5 Replies
View Related
May 28, 2004
Has anyone used DTS packages for migrating old data to a new schema?
If so are there any tutorials on this?
I'd prefer not to do this by hand. ;-)
View 3 Replies
View Related
Jul 18, 2000
I have a question regarding BCP. We are doing a BCP operation from one machine to another machine. (i.e) Flat file from the web server are BCP on to the DB server. For this operation we have mounted the FTP directory of the web server on to the DB server. Say G: drive of the DB server is mapped to the Ftp directory of the web server.
Now when we run the BCP we get the following error.
SQLState = 08001, NativeError = 6
Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Create File()).
The connection is using NAMED PIPES.
Say when we BCP out the above error message comes but the flat file is generated with out any records in it.
Are we missing any parameter settings.
Please help.
View 1 Replies
View Related
Jul 13, 2000
Hi ,
We are having 4 sql7 servers. All are in network only.
serverA: drives c,d
serverB: drives c,e
serverC: drives c,d
serverD: drives c,d,f
Now my question i want map all the drives to each other. So that i can use the space wherever available, because in serverA i dont have space but in ServerC have lot of free space.
Can anyone pls tell me in detail how we have to map the drives.
Thanks
--Siva
View 2 Replies
View Related
May 11, 2006
Hi,
I need to export data from SQL tables to AS400 files(the SQL table has the
same file name and column names as the file on the AS400) .
I created a DTS that has the following tasks: dynamic properties task, SQL
server connection, transform data task and a other connection(ODBC data
source).
I'm using global variables to dynamically set the source and destination
tables names on the transform data task. The problem is the transformations
are not automatically mapped and I get an error message when the
DTS package is executed with a source and destination that has
different columns than the ones specified in the transformation.
Any ideas or possible workaround would be greatly appreciated.
Thank you very much.
View 4 Replies
View Related
Jul 15, 2015
In my project I have user and group defined as 1 to 1 relationship, meaning one user can have only one group assigned.
I just received a change request to enable any user could have more than one groups assigned, so I am thinking how to handle this request and want to hear options from this forum, here is my preliminary thought:
1. add a new field (let's say byGroup) in User table, this byGroup is a varchar and value like 2,3,9 indicates the user has privilege to group 2,3,9
2. when doing user related info retrieval function, I need to convert the string 2,3,9 to array and then loop the array to get the right info
View 9 Replies
View Related
Apr 2, 2008
Hi Guys,here is my story.
I have a table with values stored in it and the Code Column contains sets of values that need to be mapped to a single value.For example i want the values ALMW,ARBAC to map to AL ARB and the values ARBIT,ARBOP,ARBSC to map to CU ARB and A1JAN,A1FEB,A1MAR,A1APR,A1MAY,A1JUN should map to AL AVG.
The values under Code are already in a database table and the ones under New Code are the new mappings for the values under code and are not in the database.
This table is referenced by other tables for the Code and i want those references to be done to the New Code instead.How do i modify the existing table or design a new table to preserve the current Codes and also map them to the new Code.
Code New Code
ALMW AL ARB
ARBAC
ARBIT CU ARB
ARBOP
ARBSC
A1JAN AL AVG
A1FEB
A1MAR
A1APR
A1MAY
A1JUN
In god we trust,everything else we test.
View 1 Replies
View Related
Apr 18, 2008
Hi Guys
I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.
Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.
The structure of the data is like this.
Code Name
AA AA
AL Aluminium
ALM ALM
ALT Aluminium in tonnes
AR AR
AUD Australian Dollars
AUJPY AUJPY
CAQ CAQ
CC CC
CCF CCF
CER Carbon Emmission Reduction
The mapping table is like this:
XAA AA
XAL AL
XMA ALM
XAL ALT
XAR AR
In god we trust,everything else we test.
View 8 Replies
View Related
Oct 10, 2013
I have two SQL tables
Coupon and Coupon CouponStore
In Coupon there is a column of CouponID and in CouponStore the same column name.
Also in CouponStore there is a column called StoreID that has the value of each store 1, 2, 3 and 4.
How would I write a script to map any CouponID in the Coupon table to each Store Value that are not already mapped to a StoreID?
View 6 Replies
View Related
Nov 3, 2014
The Source Data is
ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1---151---- ----- ----- Y----- -----
2---151---- -----Y----- -----Y-----Y
3---152----Y----- ------ ----- ------
4---152---- -----Y------ ----Y------
The Expected Output should be
ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1--151----- ----Y------Y-----Y-----Y
2--152-----Y-----Y------ -----Y-----
View 1 Replies
View Related
