Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
One of the readers was using windows authentication in SQL connection string. When he tries to access database from connection string from Windows Application, it was working but when he tries to access same database with same connection string, it was throwing above error.
Main reason behind this error is permission issue. Now, question pops up in your mind, if that is a connection issue than why it was allowing same connection string in Windows application? Simple logical reason is that, Windows application uses local system account to connect with database when connection string built for Windows Authentication and ASP.NET uses ASPNET user of the web-server to connect with database. If your ASPNET user don’t have permission to access database, you will greeted with error
“Cannot open database ‘databasename’ requested by the login. The login failed for user ‘Machine-name/ASPNET’”.
So, now you know this is a permission issue, as soon as you will grant access to your db to ASPNET user, you will be ok but what, if you don’t have SQL Server client tool? It is quite possible if you are having SQL Express edition.
First let us see the command which will helpful to give permission to ASPNET user.
EXEC sp_grantlogin ‘Machine-name\ASPNET’
USE ADVENTUREWORKS–(YOUR DATABASE NAME)
EXEC sp_grantdbaccess ‘Machine-name\ASPNET’
EXEC sp_addrolemember ‘db_owner’, ‘Machine-name\ASPNET’
Run above script and you are done but what if you don’t have SQL Server client tool? You have to use OSQL utility from DOS prompt to run above command.
After getting DOS screen, follow below given command.
Note: “Ritesh” is my webserver name where ASPNET user resides. “.” in OSQL command represent my databse server you can give your SQL server’s IP or Name there, like: ‘Localhost\SQLExpress’
C:\Documents and Settings\Ritesh>cd\
C:\>osql -S . -E
1> sp_grantlogin ‘Ritesh\ASPNET’
2> GO
1> use adventureworks
2> GO
1> sp_grantdbaccess ‘Ritesh\ASPNET’
2> go
1> SP_addrolemember ‘db_owner’,’Ritesh\aspnet’
2> go
So, here with you will solve your error with above command along with OSQL utility which is good utility itself.
Enjoy !!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Certificates in EE
MASTER SQL Server Apr 13, 2009
MASTER SQL Server 2005 Apr 22, 2009
GURU SQL Server Apr 26, 2009
Answer History in EE
1 Question Answered on Mar 26, 2009
50 Question Answered on APR 09, 2009
100 Question Answered on APR 19, 2009
150 Question Answered on APR 25, 2009
List is growing on and on
Moreover, I have entered in HALL of Fame for the year (12th), month (1st) and week(1st) in my very first month for SQL Server zone and in Month and Week for SQL server 2005 zone, looking forward to hold this position and enter in over all zone Hall of Fame very soon.
</vendor>
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
I wrote an article on how to upload image to SQL Server database BLOB fileld and retrieve it back. You can refer that article at:
http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html
After trying to implement this functionality, one of my readers has observed that he can’t upload the file big file. He came to me for solution. Actually in ASP.NET you can upload file with maximum size of 4MB. That is the default settings. Than how could you upload bigger file than 4096 kb? Isn’t there any way around? Because this is somehow very common need.
Well, they is a will, there is way!!! You can set maximum file size with httpRuntime tag. You can set this at machine.config or web.config but I insist, not to touch machine.config file as it has web server wide effect, you should set it in web.config so that it affect one website or one subdirectory in which web.config reside.
You have to add one line under <system.web> element of your web.config. The line is
<httpRuntime maxRequestLength=”20480″ />
This will increase file limit from 4 MB to 20MB.
Enjoy!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
I just assisted one person in one of the forum who wanted the list of all databases which are having user “Guest”. Since this is something needs to iterate between all available databases in SQL Server except “Master” and “TempDB”, I thought to do it by undocumented stored procedure available in Microsoft SQL Server 2005, Sp_MSforEachDB.
Let us see how I did it.
EXEC sp_MSforeachdb
‘if ”?” <> ”tempdb” and ”?” <> ”master”
begin
USE ? SELECT name,db_name() databasename FROM sys.sysusers
WHERE name = ”Guest” ORDER BY name
end
‘
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
One of my close friends is working on one project for Construction Company recently as a free lancer, when I visited his home yesterday, he was calculating days in C#, since I believe to manipulate data more in SQL Server I helped him in this situation and we did calculation in SQL Server itself.
Here is small part of script the script with one small situation. In big construction company, they might have many contractors to work on site; they might have one table which stores information about contractor and their start and end date. Now, I want to calculate which contractor has worked how many days in particular month. Well, it seems pretty easy, isn’t it?
You can use date difference function and sum it up, you are done. Right? NO, it won’t give you proper picture, for example if one contractor has worked from 27-Jan-09 to 5-Feb-09 than date function will display 9 days in January month but what I want is 4 days in Jan and 5 days in FEB. Let’s have a look at how to solve this by CTE, there may other ways also, if any of my reader new that, kindly give it here so that other readers can find few other ways to do the same task.
–create table for demo
create table site
(
contractor varchar(20),
startDate datetime,
endDate datetime
)
–have some date for testing,
INSERT INTO Site
SELECT ‘Ritesh’,‘2009-02-01’,‘2009-02-07’ union all
SELECT ‘Ritesh’,‘2009-02-15’,‘2009-03-15’ union all
SELECT ‘Ritesh’,‘2009-01-01’,‘2009-01-14’ union all
SELECT ‘Rajan’,‘2009-02-25’,‘2009-03-05’ union all
SELECT ‘Rajan’,‘2009-03-11’,‘2009-04-01’
–cte as a powerful solution of this situation.
WITH CTE AS
(
select contractor,startDate, endDate, startdate totDate from site f
union all
select s.contractor,s.startDate, s.endDate, s.totDate+1 from cte s
where s.totdate+1<s.enddate
)
select contractor,year(totdate) as ‘year’,month(totdate) as ‘month’,count(*) as ‘TotalDays’ from cte
group by contractor,year(totdate),month(totdate)
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com