Creating Logins For SQL Server 2005
Jan 10, 2007
I am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.
View 2 Replies
ADVERTISEMENT
Mar 8, 2006
Hello,
We use one standard account with a password that is never given out.
In SQL 2000 we had a script to extract the password in encrypted format so that it could be run as part of the installation process.
That same process does not work for SQL 2005.
Below is a sample of the script that we were deploying for SQL 2000 installations.
-------------------------------------------------------------------------------------------------------------
-- Login: CPAPP
declare @pwd varchar(50);
SET @pwd = CONVERT (varbinary(256), 0x01003402EC1BDADF45C9D788C23459BC36D73E5B2B9F2F235138F6BB8D0CD2317FCBA41EB59D191801AC287A14FF) EXEC master..sp_addlogin 'CPAPP', @pwd, @defdb = 'CPSQL' , @sid = 0xE5994FEF661AF842A0CA38AAFEB4360F, @encryptopt = 'skip_encryption'
SET @pwd = CONVERT (varbinary(256), 0x01003402EC1B9DA21489EC2D47CA9B8549255E9C9ACD4260661DFE04E9A0AFA631A27676C7A79E2DE20A52265666) EXEC master..sp_addlogin 'CPREPORT', @pwd,@defdb = 'CPSQL' , @sid = 0xD7E65E9AE7E87F4F9C72929A0B37F35C, @encryptopt = 'skip_encryption'
SET @pwd = CONVERT (varbinary(256), 0x0100A44D0526FABFF90D4CA524FA6415DD998256EB62963309D5F561AC5B116318E4F93D2159D9BAC95F356EBDC2) EXEC master..sp_addlogin 'cpdata', @pwd, @defdb = 'CPSQL' , @sid = 0xBE1AC61A2A8D8543ACAF2D403AAD96A4, @encryptopt = 'skip_encryption'
-------------------------------------------------------------------------------------------------------------
The procedure that we are using to script the logins is from the Microsoft support page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
Is this what we should be using for SQL 2005?
Please advise.
Thanks in advance
View 6 Replies
View Related
Sep 18, 2006
I need to create a new login that only has access to 2 Views.
I have tried everything, A New Role, Schema, set the Securables but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of tables. I only want this Login to be able to run either view and not see anything thing else.
View 13 Replies
View Related
May 13, 2008
Hi
How to transfer logins from 2000 to 2005?
Raj.
View 1 Replies
View Related
Dec 19, 2006
what is maximum no. of concurrent Logins Possible on a SQL server 2005 Database?
View 3 Replies
View Related
Jul 17, 2001
Hello,
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem?
Please, help
Thanks
View 3 Replies
View Related
Apr 3, 2007
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
View 2 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
Dec 4, 2006
Hi guys,
I have tried to use the sp_help_revlogin script to move logins from a sql 2000 server to a sql 2005 server. It works for users that have a password. But if the password is blank it will fail. Is there a work around for this since I can't change the users passwords?
I have also tried SSIS package but that too doesn't work because of the same reason.
Can anyone help...
View 1 Replies
View Related
Jan 21, 2007
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Any assistance would be greatly appreciated.
Thanks,
John
View 3 Replies
View Related
Jul 25, 2007
Hello, How are you?
I have a problem when I try to create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE. When I€™m going to choose the data source, the SQL Server 2005 Compact Edition provider doesn€™t appear in the list. I installed the SQL Server 2005 Compact Edition from this page http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en. In the additional information says by installing SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll). So, I don€™t understand why couldn€™t I create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE?
I appreciate your help€¦
View 6 Replies
View Related
Aug 15, 2007
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is : creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using : Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error "" Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help
View 13 Replies
View Related
Nov 19, 2007
Hello I am using SQL Server 2005 I want to delete the data from my table after specific number of days.This should happen automatically. After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.If anyone can give an example for this it will be great.Thanks in advance.
View 1 Replies
View Related
Apr 17, 2008
In 2005 the security login will not allow me to add a user, database role, application role or schema manually. If I use a Transact-SQL in Query Editor to create a Login with a password, it said it was successful yet The T-SQL added my login as an application role is this correct? and why can I not add manually? is there another function that must be performed first?
I am very new to the environment but know 2000 very well and miss it being so user friendly.
View 3 Replies
View Related
Jun 23, 2008
Hi,
We wish to create cube in SQL Server 2005,
where we can create a cube using query.
View 2 Replies
View Related
Mar 14, 2008
In oracle we create abstract datatypes using the command, for example,
CREATE TYPE address as object
(Street varchar2(50),
cuty varchar2(25));
Can this be done in SQL SERVER 2005? I am new to SQL SERVER 2005. Please help me out giving out the proper syntax alongwith an example.
View 1 Replies
View Related
Mar 21, 2008
Hi
I just need some information on creating the second instance in sql server 2005. In my production server we have the databases with different collation. we need to separate those two different collation databases by creating the new instance. I have to create the instance by this afternoon. I think we need to install the sql server 2005 again and select the option of named instace. As long as this is production server are databases are going to be available for the users while installing the sql server to create the different instance? Could some one please respond on this. Thanks!
View 11 Replies
View Related
Feb 10, 2008
Hello all,
I am new to MS SQL Server and am having troubles figuring out how to create a new database file. I have installed everything I can and and still cannot find any where to add a database file. I am upgrading from Access to SQL and feel like I am missing something... I will eventually need to create a couple of SQL databases and connect from Classic ASP and ASP.net in different applications. Before I worry about how to connect I need ot create something to connect to.
Any help would be greatly appreciated.
Thanks
Panhead
View 1 Replies
View Related
Jul 1, 2006
I have two problems on creating new SQL server database in Visual Web Developer 2005 Express using SQL server 2005 Express.
The 1st problem: (It is resolved)
After installed SQL Server 2005 express and Visual Web Developer 2005 Express. When I tried to create a SQL server database in VWD the following error occurred:
"connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251"
I was stuck with this problem for few days. I did several times of install/reinstall of VWPExpress and SQL server Express, made very sure following proper de-installation sequence as specified in the release readme. But it did not resolve the problem.
Latter I found out that if I login with another Windows XP user account, I don't get the problem.
Changing the security authority of XP file systems and IIS didn't help.
And to prove that I was doing the installation correctly, I tried a new installation of the VWDExpress with SQL server 2005 express in another machine, it worked and I could create new empty SQL database.
After 2 days of frustrating changing of options, parameters, server names, messing with the registry etc.... Finally, I found a way to overcome this problem.
The solution is:
In Visual Web Developer 2005 Express, select:
Tools -> Options -> Database Tools -> Data Connections,
Change the setting: "SQL Server Instance Name (blank for default)" to "SQLEXPRESS". (where SQLEXPRESS is the default server instance name)
That solved this 1st problem.
I still don't know the exact reason for this problem. But I recalled I did installed the VWDExpress few weeks ago, I might have installed it without selecting SQL server 2005 express at that point. I guess the subsequent install/re-install did not clean up those junk in the registry.
I still have the 2nd problem:
The "Create new SQL database..." option is always grayed out when I right click on the Data connections in the "Database Explorer" tab.
I have a bypass for this difficulty for time being:
I have to do it by using the "Add connection....", put in the server name: "myserver/SQLEXPRESS" and a new database name.
Any input form anyone ? Appreciated!
View 3 Replies
View Related
Feb 8, 2007
What is the relationship between the users in a database and the logins on the server.
View 1 Replies
View Related
Jun 22, 2007
As from what i see there are different system files for logins.
Is there a way to transfer SQL 2000 logins to SQL 2005 i am used to the
sp_help_revlogin i think thats the script..in 2000
Is there a way to convert all logins from SQL 2000 to SQL 2005
View 3 Replies
View Related
Feb 5, 2008
Hi all,
I am using SQL 2005 (Enterprise Edition, SP2) Mirroring with a Principal, Mirror and Witness set to High Safety mode and am a little confused!
As far as I understand it, during a failover, although the database content will automatically be available on the Mirror any user accounts will need to be either manually created or manually mapped to the new principal. Is this the case? If so, I can't really see the purpose of using Mirroring (in my scenario anyway).
For example - We principally use the database servers for our web applications which we connect to using ADO.NET. Each database and Web app has a different SQL user account to allow access etc. If we experience a failover this means the users will not be able to use the web app until we have manually recreated/mapped the SQL accounts for each and every database. Surely this is a massive oversight?! Or am I simply getting the wrong end of the stick.
Any help/guidance would be greatly appreciated!
Thanks,
Richard
View 3 Replies
View Related
Sep 23, 2007
I have several general questions about this but I thought I would describe what I am doing first. My senior project team is developing a web application that will wind up using SQL Server for the database. We are writing it in ASP.net and developing the database on SQL Server Express. My job is to develop and deploy the database. I have an ERD designed and am getting ready to start coding the tables, constraints and stored proceedures but am unsure of a couple of things.
1) How do I create a new schema?
2) I can see how to create tables in the GUI but what I am trying to do is create scripts that can be used to create the tables and objects on the actual server that our client will be using. How do I create scripts in 2005 Express? Do I just go click on the new query button?
3) Are scripts that work for Express going to have any problem executing on SQL Server?
View 1 Replies
View Related
May 30, 2007
Hi All... We're cloning an application currently under development on Server 2003 / SQL Server 2005 / dotnet 2.0. The clone's going to live on an XPSP2 box with SQL Server 2003 - it's for demo purposes... The application's able to get to our own database, but it's having a hard time creating the ASPNETDB that dot net likes to have.
Aside from the OS, the only difference between the two SQL Servers is that on the working system, we're using windows authentication on the database while on the non-working system, we're using SQL Server authentication. But we've tuned the connection strings appropriately - again, we can get to our own db just fine.
We have the following lines in our web.config:
<connectionstrings> <clear /> <add name="LocalSqlServer" connectionstring="Server=localhost; Database=ASPNETDB; UID=sa; PWD=mypassword" /></connectionstring>
On the working system, the Server is an actual IP address, integrated security is set to true, and of course the uid/pwd is a bit different. But again, using a very similar connection string, we able to get to OUR database on the non-working system...
The error we're getting at runtime is:
Cannot open database "ASPNETDB" requested by the login. The login failed. Login failed for user 'sa'.
When we poke into the database from SQL Server Management Studio - on a remote machine - we can see our own database is there, but the ASPNETDB isnt there. Yet... I suspect it failing getting in to just create the db....
Any thoughts? Thanks for the help!!! -- Curt
View 2 Replies
View Related
Oct 29, 2007
Hi friends, Is there any way to create a text file or word file through sql server 2005.If you have any source please suggest me.. P.Kumaran
View 3 Replies
View Related
Jun 13, 2008
When creating a user in SQL Server what is a Schema as opposed to a Role?
In the security folder of a database, I also saw the folders "Asymmetric keys", "Certificates", and "symmetric keys". What are these about?
thank you.
View 1 Replies
View Related
Feb 1, 2008
I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?
One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.
With these information, can some body help me in deciding this?
View 2 Replies
View Related
Mar 14, 2008
In oracle we create abstract datatypes using the command
CREATE TYPE address as object
(Street varchar2(50),
cuty varchar2(25));
Can this be done in SQL SERVER 2005. I am new to SQL SERVER 2005. Please help me out giving out the proper syntax alongwith an example.
View 1 Replies
View Related
Mar 11, 2008
I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.
View 3 Replies
View Related
Nov 28, 2006
Hi,
For various reasons a new desktop application I am developing is using the SQL Server 2005 Compact Edition Release Candidate to store local data. On my development machine (XP SP2, VS2005, SQL Server 2005 Developer Edition) the software is able to create the database file, and read and write to it fine.
However on our test machine, which does not have any of the development tools the software is unable to create the database. A System.Data.SqlServerCe.SqlCeException is thrown. The message is: "SQL Mobile usage is restricted on this platform. To use SQL Mobile, you must install SQL Server 2005, Visual Studio 2005, or the Tablet PC SKU." Obviously this goes somewhat against the blurb for SQL Server CE.
Is this something that will be fixed shortly? Or is it the intention that databases can't be created on machines that do not SQL Server or Visual Studio installed? (Incidentally the reason for creating the database on the fly is that we are encrypting it, using the users credentials, so that only they can open the database.)
Thanks, Steve
View 4 Replies
View Related
Nov 14, 2007
Hi,
We have recently been looking into creating our SQL Server 2005 database as a unicode database using UTF-8 encoding. However initial search's suggest that SQL Server 2005 does not support the creation of UTF-8 db's and only supports UCS-2. Is this the case? If so then why is it not supported as most other vendors (Oracle, IBM) support it. If it is possible to create a UTF-8 database could you provide some details on how to go about this?
Thanks,
Cathal
View 1 Replies
View Related
Mar 17, 2008
I was wondering if anyone could help me with this. I am currently working on a project for reporting on a new acquisition the company have made. Unfortunately, the people I have to give access to the SQL Server are using Netware for authentication.
Is there any way to allow these people to log into SQL Server without having to create a SQL authenticated user for each of them?
Thanks
View 7 Replies
View Related
Jul 25, 2006
Hi, all.We have a couple of pathological sql servers that have lots and lots ofpage faults per second, up to 4000. Our client programs are written inC#/.NET 1.1 and utilizes connection pooling.Some of the client programs seems to log in hundred of times persecond, as reported by perfmon->.SQLServer:GeneralStatistics->Logins/sec. Stopping the client programs reduces thatnumber significantly.We've done code reviews of the client programs and they look OK.Monitoring .NET connections&pools does not show anything suspicicous.We're currently rewriting the clients to use one db connection insteadof the pools, but that takes some time and may introduce bugs. Doesanyone know why we have these problems and/or why logins/sec is sohigh? I'm thinking "bugs in the .NET client", but really have noidea...One thought I had was that the Page Faults reported for sqlsrv.exe isrelated to memory mapped IO and therefore can be ignored. Right orwrong?Any thoughs/pointers/ideas, even wild guesses, are most welcome.BjørnPS: The server memory is fixed at 1.5GB out of 2GB physical ram,clients run on the same machine and use TCP/IP comm.(I know...) Thehost itself is not paging.
View 4 Replies
View Related
