Weird Behaviour With Logins At Server Level
Aug 27, 2007
Hi,
This thread is a reformulation of a prior thread.
I created a login 'Network service' at server level in Management Studio
express.
I use windows authentification.
Then i defined an user for my database which is associated to login 'Network
service', because the application asp.net uses that account (IIS 6.0). This
user received db_read and db_write roles.
This works.
Now i experimented a little bit and i removed from the logins at server
level the login 'Network service'.
Result: the application still works.
.
Then i removed the Builtinusers login from the login list at server level.
Result: i get the error: "login failed for Network service".
I recreated then the login 'Network Service' at server level but not the
Builtinusers login.
Result: it works again.
My conclusion is: one of the two logins must be in the list: Network Service
or Builtinusers
Is this right?
Why do i get that error when both logins are removed and not only when
Network Service is removed?
Thanks
View 1 Replies
ADVERTISEMENT
Jul 5, 2004
When I run the command:
exec master..xp_cmdshell 'NET USE'
from the analyzer the box responds there are no entries in the list.
After that, I run the command:
exec master..xp_cmdshell 'NET USE Z: /DELETE'
after which the box responds with a "network connection could not be found."
and that's all okay.
The weird thing is:
exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME'
results in a "The local device name is already in use.".
The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.
I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?
thanx,
View 5 Replies
View Related
Sep 20, 2007
I have 2 exact same sql tasks in different packages. Connection manager is defined for the same database for both. one of the sql task works, and other one throws out this error:
[Execute SQL Task] Error: Failed to acquire connection "pdsprod.pdsdataread". Connection may not be configured correctly or you may not have the right permissions on this connection.
this is completely mind boggling. I have compared both sql tasks for each and every property and they are exactly the same. what is going on?
by the way I am on 64 bit box with Run64bitruntime= false.
View 5 Replies
View Related
Mar 13, 2008
I am using SQLExpress for Unit Testing my application. In the Unit Tests, I use a local database file that is attached automatically in SQLExpress when the Unit Test uses it.
FYI, in the Unit Test I use the following connection string :
"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MyUnitTestDatabase.mdf;Integrated Security=True;User Instance=False;Pooling=false"
By accident, the MyUnitTestDatabase.mdf file was marked as ReadOnly. So, after executing several times the unit Test, the attached databases appear in grey in the SQL Server Management Studio Express. That's normal ! The problem I want to report here occurs when I execute the following script in SQL Server Management Studio Express:
use master
go
sp_MSForEachDB 'Print ''?'''
go
In .SQLExpress, I currently have 8 databases (The three last databases are those attached by the unit tests. They are ReadOnly):
- master
- model
- msdb
- tempdb
- MyUnitTestDatabase (the original db copied and used by the Unit Tests. It's not ReadOnly)
- 1E6AA4A60F3733D37F016842D4626B8B_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_22_03OUTMYUNITTESTDATABASE.MDF
- ADA9F382DFBC95C8334EF95336C98274_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_13_57OUTMYUNITTESTDATABASE.MDF
- F00BF38C8BB8F07D37FCC4E918CF815E_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_10_04OUTMYUNITTESTDATABASE.MDF
When executed, the script mentioned above displays sometimes all the databases and sometimes only the 4 first databases ?!?!
I did a demo to various colleagues here, pressing F5 many times in the Script windows. It's seems taht it displays 4 names or 8 names "at random"... (I always wait for the message "Query executed successfully" before pressing again F5).
I have to understand the problem here because I use sp_MSForEachDB to detach all the databases at the end of the Unit Tests and it also fails from time to time...
Thx in advance for any tip that could help me in finding the origin of this problem
V.
PS. : FYI, here is the stored proc I use to automatically detach the databases at the end of the unit tests
declare @spid int
declare @killstatement nvarchar(10)
IF @database like '%TESTRESULTS%'
BEGIN
-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select request_session_id
from sys.dm_tran_locks
where resource_type='DATABASE' AND DB_NAME(resource_database_id) = @database
open c1
fetch next from c1 into @spid
while @@FETCH_STATUS = 0
begin
-- Don't kill the connection of the user executing this statement
IF @@SPID <> @spid
begin
-- Construct dynamic sql to kill spid
set @killstatement = 'KILL ' + cast(@spid as varchar(3))
exec sp_executesql @killstatement
end
fetch next from c1 into @spid
end
close c1
deallocate c1
exec msdb.dbo.sp_delete_database_backuphistory @database
exec master.dbo.sp_detach_db @database, 'true'
END
END
Possibly I was not detaching the databases in a "clean way" and my system databases are now corrupted ? Is such a case, what should I do in addition to the code here above to correctly detach the databases ?
View 3 Replies
View Related
Mar 27, 2007
Hi all,
I'm encountering a weird behaviour:
I have a .NET application that should allow to select Server and Catalog to install a DB.
I create a mask similar to the one in SQL Server, so i have to fill the Server DropDownList.
This is my code:
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();
foreach (System.Data.DataRow row in table.Rows)
{
string strDBName, strInstance = row[1].ToString();
if ((strInstance == null) || (strInstance == ""))
strDBName = String.Format("{0}", row[0]);
else
strDBName = String.Format("{0}\{1}", row[0], strInstance);
Program.WriteLog("Row: " + row[0] + ", " + row[1] + " (" + row[3] + ")");
}
Program.WriteLog just writes a string on the log file
Sometimes I need to restart the SqlServer so I Run
CtrlDatabase = new ServiceController(strServiceName);
CtrlDatabase.Stop();
CtrlDatabase.Start();
With the obvious checks
Well the first time, before restarting, the log file is
Row: WKS08, COMPACS (8.00.194)
However if i run Stop & Start the log file is
Row: WKS08, ()
SqlDataSourceEnumerator seems to be not more able to read data.
Why? How can i fix it?
View 3 Replies
View Related
Jul 7, 2015
Does securityadmin Server level role can add, alter logins and corresponding users on all databases ?
If not what is the best role other thn SA to have to manager logins and users.
View 4 Replies
View Related
Sep 16, 2014
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 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
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
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
Jun 4, 2008
Hi all,
I want to share an experience I made in the last few days and like to hear your comments about it. I am developing an ASP.Net 2.0 Web Application using SQL Server 2005 on my local system. After implementation was done, I had to deploy the application to the production server. Because of license problems, on the server is the express edition of SQL Server installed. The system worked fine for about 2 month. But the last week we noticed, that there was deadlocks in the application. After searching a while I noticed, that there were a lot of open connections. When you open SQL Server Management Studio and look at Management > Activity Monitor, you can see all opened connections in the connection pool.
So the problem was, that with every request, a new connection was created, instead of using the existing ones, even if the state of the connections was sleeping. On SQL Express, if a specific limit of connections is reached, it'll wait for a connection to be released, but there is no release, so it threw a timeout error. But suprisingly, on SQL Server there also were a lot of connections created, but there were never a deadlock, which I can't explain. Also I can't explain, why it also worked for 2 months on SQL Express.
The architecture:
I have data classes, which are implementing IDisposable. In the dispose method, I call Dispose on the connection and set it to null. And in code I instanciate my data classes in using blocks. So on reaching the end of the using block the data class instance is disposed. In the dispose method the connection is disposed. So I thought, that everythink will work fine, but it doesn't.
The problem was solved by calling Close() on the connection in the Dispose method in my data class just before calling conn.Dispose().
So does this make sense to you? The fact, that it solved my problem lets me believe to that solution, but I can't really say why. So if you have any ideas or knowledge, I'd love to hear it.
Regards,
Koray
View 2 Replies
View Related
Jun 2, 2004
Hello,
I've set up a basic login page that grabs some data from sql server. I've set up an sql server user for the connection and it all works fine.... until now that is... I get an "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied." error..
Here's the weird thing though - I'm storing my connection string in web.config. If I open up web.config and re-save it, my login system works again - for a while anyway. Then it goes back to not working. When you save a web.config file, is it compiled in any sort of way? because when I do save it and run my web page, there's that sort of delay the first time round like there is when you update an aspx page or a dll. ? Anyway - it's as if that process sorts out my problem but only for a an hour or so and then the problem happens again.
I'm not making any changes to the web.config, just re-saving it. And everything else works fine, for a while at least, then the sql error happens.
Any ideas?
Thanks.
View 2 Replies
View Related
Apr 12, 2006
Hi, I just installed SQL Server 2005 EE. After installed it, I took a
look at the program. However, I found out that SQL Server 2005 EE
doesn't any program that you can create your database on. There nothing
like Ms. Access where you can run the program to create access database
file. SQL Server 2005 EE doesn't have something like that so that I
will have to create the database file under Visual Studio 2005
applications such as VWD or VB.
Is this just me or is that the way SQL Server 2005 EE is?
View 1 Replies
View Related
Sep 21, 2001
I have an application which is a SQL Server 7 back end with VB 6 SP5 front end.
For some of my users, when the VB code calls a particular stored procedure, it causes VB to die without executing the stored procedure. I am connecting via ADO.
If I execute the same stored procedure via Query Analyzer on the same machines it works perfectly.
The same code also works perfectly on all the development machines.
The error only occurs for a particular stored procedure. Other stored procedures execute perfectly on the users' machines.
Any ideas?????
View 2 Replies
View Related
Jul 31, 2002
I have a weird problem!
I have a database which takes up 12.5Gb of disk space yet when I drop all the objects out of it, it says that I still have 9.5 Gb of disk taken up by the database!
The database itself is nothing special. There are a couple of inefficient tables (char rather than varchar etc) and one that contains a column of datatype "image" but other than that it is pretty good.
Can anyone help?
View 2 Replies
View Related
Dec 4, 2007
Hi Guys,I have got a weird problem which I have never faced. I have a table which has 3.8 million records. I found this information from sysindexes since I could not find the count using the Count(*). After breaking my head for a while I tried to use the Top function to get the results and I did get them but till 630000 record not a record more than that. I tried DBCC CheckDB as well as DBCC Checktable with no success. I would really appreciate if anyone can solve this problem.
View 6 Replies
View Related
Jul 20, 2005
Here's a weird one.I'm running SQL Server 7 and when I run a backup something weirdhappens. When I perform the backup via Enterprise Manager by rightclicking on the database I want to backup, I click on OK but noprogress blocks show up in the window showing you the status of thebackup. The completion window pops up saying that the DB has beenbacked up. OK--Fine, maybe the backup is really quick. Then, throughExplorer, I look at the directory where the database backup is placed.It reads 0 KB - but - about a minute later the the size of the backupchanges to the correct size. Seems strange, wouldn't the "completed"window show up after the backup is out there??Well, to make matters a little more interesting, when I define thisone database in a maintenance plan, the plan will complete but nobackup is present! The log file shows it runs OK - and - when I runthe plan through query analyser it says OK, too. But, no backup ispresent.What gives?
View 2 Replies
View Related
Aug 2, 2006
I was able to successfully create a database maintenance plan for SQL Server 2000 Transaction Log Shipping for a few databases a few weeks ago. Yesterday, I created a few more but to my surprise, I can no longer do it. I can create a maintenance plan but the job it creates does not start even if I force the job to start. I did exactly the same thing as what I did (as I document everything I do) before but no luck.
Has anybody had this experience before?
View 5 Replies
View Related
Dec 18, 2006
Execute following T-SQL within Queary Analyzer of SQL Server 2000:=======================================DECLARE @dTest DATETIMESET @dTest='2001-1-1 1:1:1:991'SELECT @dTestSET @dTest='2001-1-1 1:1:1:997'SELECT @dTestSET @dTest='2001-1-1 1:1:1:999'SELECT @dTest=======================================You get what?This is my result which is weird:2001-01-01 01:01:01.9902001-01-01 01:01:01.9972001-01-01 01:01:02.000Then what's the reason of this weird problem?
View 3 Replies
View Related
Jan 16, 2008
I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
View 1 Replies
View Related
Nov 19, 2007
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance
View 1 Replies
View Related
Mar 27, 2006
Hi guys,
We've had Reporting Services running in a production environ. for 6 months fine, but from Saturday every report now causes the following error (in both the Report Manager and Soap calls):
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name: date
Now, before you jump to conclusions - this error is occurring on reports with both parameters and no parameters (ie in reports that have no "date" parameter in the report).
The next bit of info is the weird bit...
It was working on Friday (25/March/2006) - so as a test, i switched the servers clock back to Friday - and BINGO... it worked. Then I changed it to Saturday (26th March) and it doesnt work. In fact for the next 7 days - the service will not work until April 2nd 2006 - (when I changed the systems date to the 2nd it worked again.) Moving forward, it looks like its working fine.
Does anyone have any suggestions? This is in a production environment, so obviously changing the sytsem date as a quick fix workaround wont suffice.
Thanks in advance.
Grey
View 1 Replies
View Related
Oct 17, 2000
good afternoon,
Does any1 know how to prevent a login screen being encountered upon hitting the SQL dbase? I'm sure there is a table in SQL server that you can create that allows this (and the number of rows retrieved as well). Can any1 help me out?
Thanks in advance for any and all help
gurmi
View 3 Replies
View Related
Aug 21, 2002
I have succeeded in reattaching the db to the new server, but the old logins do not work, as has been experienced by many people.
What follows was is a solution from an earlier post (last year) on this topic. I tried running the queries from Master in on the new server, and got the following error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'sysusers' with unique index 'sysusers'.
The statement has been terminated.
The sql queries I am using are:
insert sysxlogins(srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language)select srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language
from "oldServerName".master.dbo.sysxlogins
where name not in ('DISTRIBUTOR_ADMIN', 'REPL_PUBLISHER', 'REPL_SUBSCRIBER', 'TRACEUSER', 'SA')
AND
insert sysusers(uid, status, name, sid, roles, createdate, updatedate, altuid, password)
select uid, status, name, sid, roles, createdate, updatedate, altuid, password
from "oldServerName".master.dbo.sysusers
where name not in ('public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader','db_datawriter', 'db_denydatareader', 'db_denydatawriter', 'guest', 'dbo')
The old server is set up as a linked server on the new server.
This suggests that the information is all in the new server. Any tips? TIA D. Lewis
View 2 Replies
View Related
Sep 10, 2001
We are in process of migrating of databases from one Server to another, Can anyone please suggest me how to transfer or copy the logins from one Server to the other instead of creating them all over again??
Thanks in advance...
View 1 Replies
View Related
Sep 13, 2001
Hi,
Can anyone help me to script sql server logins.
Thanks,
ravi.
View 2 Replies
View Related
Dec 12, 2001
I need to restore the logins from prod server "A" to a dev server. The dev server has many databases from different prod servers. If I restore the master from prod A to dev, i'll lose the logins on dev for the other dbs. How can I get the logins from prod A to dev without losing the other logins for the other dbs?
Your help is greatly appreciated!
View 1 Replies
View Related
May 7, 2008
Can someone remind me where Server Logins are stored in SQL Server?
View 7 Replies
View Related
Oct 26, 2005
I have an application that runs off an sql server 2000 sp3 database. I have taken a backup (.bak file) of the production database and restored it to another server for testing purposes. the users cannot login to the new server because the logins have not been transfered to the new server. I am looking to transfer the logins using the procedure outlined at the following link: http://support.microsoft.com/kb/246133/ under the header "Create and Run Stored Procedures in the Master Database". what I need to know is will this procedure affect the production server in any way? (I can't have that) and is there anything else I need to do after this to let the users login to the test server with the same name and passwords?
thanks for any help
View 4 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
Apr 1, 2006
Historically, many of us have used bulk copy to move selected contents of syslogins from one server to another so that the logins stayed in sync for things such as replication, log shipping, etc.
In SQL 2005, I have no access to passwords (this is proper from a security perspective) so I can't generate a file with bulk copy. I also can't update sys.server_principals, so it doesn't really matter that I can't export the passwords.
So what is the SQL 2005 solution? Suppose I decide to mirror my database. How do I get the logins that are users of that database over to the mirror server? Scripting the logins won't work because it generates a random password. sp_change_users_login requires me to provide a password but then I would have to know each login's current password or give them each a new password. This is viable with 2 users; it is not viable with 2000!
I know that Copy Database Wizard will move the logins with the passwords intact, but it will assign new SIDs to SQL Server authentication logins. That's fine as long as it is a one-time move but not if I am maintaining a warm standby.
This problem has been one of the most frequent problems posted on newsgroups, etc. in the past. But we have had solutions. I fear that we do not have one in SQL 2005.
I'd appreciate any help you can provide.
Thanks,
Sharon
View 4 Replies
View Related
Nov 30, 2005
I am replacing a server so I need to migrate everything. The old server is running SQL2000 and the new server is running SQL2005. I am trying to write an SSIS solution to migrate everything for me and I can't even get started because I get the error "The source server can not be the same as the destination server". At the same time I am changing the name of the Domain so the two machines arenot even members of the same Domain. I am doing this over the Internet so the machines are not even on the same subnet. The only thing I can think of is that the machine names are the same so even though the domains are different therefore the full names are different, the NetBIOS names are the same. Could that be the problem?
View 1 Replies
View Related