We have a person who CAN connect to a named instance in SQL Server Management Studio. There is nothing in the log for a failed login for him. However, when he tries to expand the Tables folder under the only user database in the instance, he gets the error: Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum). Additional Information: An exception occured while executing a Transact-SQL statement or batch. Select permission denied on object 'extended_properties',database 'mssqlsystemresource', schema 'sys'(Microsoft SQL Server, Error:229).
He is trying to expand the Tables folder under a database named ADSALLDB. He can see the folder as well as the other folders (i.e. Views, Synonyms, Programmability,etc.), but can't expand any of them. He can expand the folders under the system databases.
Other uses set up just like him can connect. He is set up with read/write access to the database ADSALLDB. He can expand the system databases.
This is a named instance in a 6 node cluster. SQL Server 2005 SP1 Build Level 2221.
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
I need to expand tempdb database. The users keep getting this error when they try to do a sort: SQL Server message(1510): state 2 Sort failed. Out of space or locks in database 'tempdb'
Also in the error log I am getting these errors: Error 1105, Severity: 17, State: 1 Can't allocate space for object '-641' in database 'tempdb'because the 'system' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
What I am wondering is if expanding tempdb will fix the problem, or is there another fix that I should be looking at.
Any direction you could give me on this would be appreciated. I have very little experience with SQL 6.5
Has anyone had an issue with SQL Server not being able to expandagainst a RAID 5 file system? My current configuration is that theserver is started and stopped using the local system account. I haveonly one database (besides the master, model,etc)on the server. Whathas happend to me several times is that the primary database inquestion try's to expand the main datafile for the database (.mdf). Isetup the database to not expand automatically initially so that I canbe sure that we have enough file system space. Becuase of problems withthe application I decided to automatically expand. The other day thedevelopers came to me indicating that the databse was full and neededto be expanded. Knowing that the database was in automatic expandingmore I was surprise to hear this. I went into EM and attempted toexpand first the log and it would not indicating that it there was anissue in attempting to do so. I have never heard of a database notbeing able to expand. I ran DBCC's, etc and it came up clean. I triedto back the database up to disk and it would not backup. I finally hadto rename the datbase and rebuild it using DTS and scripts. I thoughtI had fixed it only to find out today that it (again) won't expand. Irenamed the datbase and then tried taking an older backup file andrestore it and it would not restore. This problem seems to be relatedto the file system but how I do not know.So, I am ready to run rebuild master but I have sone this before onlyto have this come back on me. I am at a complete loss. In the past Ihave had to rebuild the entire server and database from scratch. Theonly problem is that this has been done 3 times now with no completesolution or explaination. If any of you have seen this type ofbehavior and know whats going on please, please let me know what youthink the case and solution is!
I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.
I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.
Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.
The following procedure will display the size of all the user tables in a database.
CREATE proc sp_tablesize as if exists (select * from sysobjects where name = 'sp_tablesize') begin goto calculate_tablesize end else begin CREATE TABLE #SpaceUsed ( TableName sysname, TableRows int, TableSize varchar(10), DataSpaceUsed varchar(10), IndexSpaceUsed varchar(10), UnusedSpace varchar(10) ) goto calculate_tablesize end
declare c1 cursor for select name from sysobjects where xtype='u' open c1 fetch c1 into @tablename while @@fetch_status = 0 begin set @cmd='exec sp_spaceused['+@tablename+']' insert into #SpaceUsed exec sp_executesql @cmd fetch next from c1 into @tablename end
We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.
For example, to query a table called Page we would nee to do this on the new box.
SELECT * FROM webdev.page
unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?
Thank you for your help...sorry for the newbie type question.
Hi, How to ennumerate the Rights of an User for all the tables [Select/Insert/Update/Delete] in a database or how to ennumerate/list all the Table rights for a particular user in a database? By User, i mean the Login names [like bill, sam, sa] and not dbowner, public, etc. thanx in advance.
I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
Hi all, I have read/studied (i) Working with Databases in Visual Web Developer 2005 Express in http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/vwd.aspx, (ii) Xcopy Deployment (SQL Server Express) in http://msdn2.microsoft.com/en-us/library/ms165716.aspx, (iii) User Instances for Non-Administrators in http://msdn2.microsoft.com/en-us/library/ms143684.aspx, and (iv) Embedding SQL Server Server Express in Applications in http://msdn2.microsoft.com/en-us/library/ms165660.aspx. I do not understand the concepts and procedures to do Xcopy and User Instances for non-administrators completely-I do not know how to connect to databases and create database diagrams or schemas using the Database Explorer. I have a stand-alone Windows XP Pro PC. I have created a ChemDatabase with 3 dbo tables in the SQL Server Management Studio of my SQL Server Express and a website of my VWD Express application with an App_Data folder. I am not able to proceed to use Xcopy and user instance to bring the 3 dbo tables of ChemDatabase to my App_Data folder. Please help and give me some detailed procedures/instructions to bring the 3 dbo tables of ChemDatabase (or ChemDatabase itself) from the SQL Server Management Studio Express to the App_Data folder of the website of my VWD Express project? Thanks in advance, Scott Chang
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Cannot open user default database. Login failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. 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 user default database. Login failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
Hi, I'm new to SQL Express 2005. I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue. I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....". Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively. My connection string is: "Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...
We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.
The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.
I have checked for orphaned users and the user I am logging in with from the webpage is not listed.
So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.
I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.
While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.
I cannot now reset that database to Multi User, even by executing the query
We have a new production server with SQLserver 2005. Our department responsible ofr the installation made it as secure as possible, however. I have to put a new backup on to the server from the old production server. We ran into a problem when we removed the old database. I could not longer logon to the server with an authenticated user.
"A connection was successfully established with the server, but an error occured during the login process" "Shared memory provider : error 0 : No process is on the other end of the pipe"
Strange thing is when i create a new database with the same name. The user can logon to the server again.
I've created a report with drill downs that are hidden initially. While viewing the report in preview, I'll expand the drill down to see the data.
If I go and print the report, the report is printed as collapsed (which is the initial setting) rather than expanded (which i expanded in the preview).
Is there a way to print in the "what-you-see-is-what-you-get" kinda way expanding and collapsing drill downs?
I posted last year about getting and Expand All/Collapse All link working in reports, and it does work perfectly for on-demand reporting. I was able to get these working using the boolean report parameter and then the Jump to Report option going back to itself changing that parameter.
The issue that I have just discovered is that when a report with these Expand All/Collapse All links are in a report snapshot, clicking those links will cause it to re-render the report from the datasource at the time the link is clicked. This is an issue for one of the datasources we are using because the data is always changing, so when it goes back to re-render the counts and data returned are completely different then they were when the snapshot was first created. As time goes on the data will no longer even be in the datasource since it can only maintain 4 months of data.
Is there another way to get all detail groups to expand/collapse at once that does not require the report to be completely re-rendered from the datasource?
I would like to have a report that globally sets the visibility (or expanding and unexpanding) of a group. Right now, I have the + and - signs for a group, but have to select each on individually in the report.
I am trying to deploy a package to SQL. However, when I open Integration services I can not expand MSDB folder. Error is; SQL Server specified in SSIS service configuration is not present or available.... Login time expired .....(MsDTSSrvr)
I have found post here that says MsDtsSrvr.ini.xml file needs to be changed from
I tried MyServerName - does not work. I also tried value from connecting to Database Engine ( MyServerNameSQL2K5LOCAL) - does not help either.
What am I doing wrong?
Also, I need some documentation on deploying packgaes using File System.
I have saved Copy of the Package as File System location.
When I doubleclick the .dtsx file, Connection Manager holds my local SQL info. Goal is to deploy this to Development and other environments. Can this be handled some other way so I do not have to modify this file on every environment?
Also, this Execute Utility Package says at the top to use this utility to run SSIS on local computer.
So far, help on MSoft sites did not really help me a lot.
this is what I am using:
Microsoft Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200) Microsoft .NET Framework Version 2.0.50727
Installed Edition: IDE Standard
SQL Server Analysis Services Microsoft SQL Server Analysis Services Designer Version 9.00.2047.00
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 9.00.2047.00
I was wondering if anyone can help me out with this. Is there any way that you can have a document map in SQL Reporting Services expand on load, or if there is a way to programmatically set the Document Map nodes to be expanded when a report is generated.
i'm using the Enterpirse library logger to write logs into a database. When choosing connection string i choose the database i want in the "connection properties" dialog box and push 'Test connection' button. everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things, from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore.... it only work when i restart the computer.
I have a report that is grouping phone calls by "Category" (Outgoing, Incoming, Voicemail, etc). When the report is rendered, each category (along with totals) is shown and the details (each actual call) are hidden. Using toggling, I allow the user to burst open any category to view all of the call details.
I join the query for this report so that even if a category has 0 calls for the day, it is still shown. When expand a category with no calls I get a blank record (as I should, because there are no call details).
The question I have is ... Can I have the toggle be conditional? Can I have the ability to toggle thr group only when there are call details? Can I have the + to the left of the category name not be shown?
Thanks in advance for any help! Merry Christmas!!!
