| Author |
Topic |
|
cesark
Posting Yak Master
215 Posts |
 Posted - 2006-09-07 : 05:46:21
|
| Hello,I use SQL 2000 Server in my web app in a Windows 2000 Server, and I created tables and stored procedures under my user in that server (ServerName/myUserName). When I run my web app under myUserName all works fine, I mean all operations with the DB, but when I run web app under Windows 2000 S. Administrator mode, the app can’ t find stored procedures created under myUserName. Somebody knows why this happens? When I see all my Stored Proc in SQL Server Enterprise Manager, in owner column appears ‘ServerName\myUserName’How can I solve this so that I can use also the Stored Proc from my web app in Windows 2000 Server Administrator mode? Thank you |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 05:47:51
|
| It depends on the connection string you use. Post it here.Peter LarssonHelsingborg, Sweden |
 |
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-07 : 05:55:31
|
| This is my connection configuration in web app:<configuration> <appSettings> <add key="myConnection" value="Persist Security Info=False;Trusted_Connection=Yes;Data Source=ServerName;Initial Catalog=databaseName;" /> </appSettings>And this is how I connect to DB from web pages:Dim strConnection As New SqlConnection(ConfigurationSettings.AppSettings("myConnection")) |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-07 : 06:08:34
|
| Is this problem of connection string??... I can connect successfully to a DB, the error message of web app says that can’t find the SP, not the DB or connection. I think it’ s more a problem of permissions or something similar |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 06:11:54
|
| When changing the login from YOU to ADMINISTRATOR, the context is changing too.1) You grant Windows ADMINISTRATOR user the same rights in the datavase as you haveor2) You create a user in SQL and use that user in the connectionstring. Then it doesn't matter which user you are logged in with.Peter LarssonHelsingborg, Sweden |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-07 : 06:33:59
|
| Then, if it isn’ t a problem of which user I am logged in (ServerName/myUserName or ServerName/Administrator), Why the stored procedures are not found when I am in Administration mode? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 06:51:57
|
| See my previous post, paragraph 1.Peter LarssonHelsingborg, Sweden |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-07 : 10:37:51
|
| Ok, but how can I change Windows Administrator User rights in SQL Server Enterprise Manager? I can' t find it..Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 10:42:47
|
| You can add Windows Administrator account in Enterprise Manager and give him the rights to do whatever you fell necessary in the database.Peter LarssonHelsingborg, Sweden |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-07 : 11:09:35
|
| Into my database, I right click to Users icon, and I choose New database user. In Session Start Name field I choose <new> and the Session Start Properties window appears. Here, in Name text box I type “ServerName\Administrator” which is the real windows 2000 Server Administrator account (or I choose it from the list), after that I accept the form and this error always appears: Error 15401: ‘ServerName\Administrator’ user or windows NT group cannot be found. Verify the name. I don’ t understand what is wrong.. |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-07 : 11:48:56
|
| One important thing I see, is from Windows Administrator account I can use Stored Procedures which owner is 'dbo', but I can' t use those which owner is 'ServerName\myUserName' |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-07 : 12:26:06
|
quote:
Originally posted by cesark
One important thing I see, is from Windows Administrator account I can use Stored Procedures which owner is 'dbo', but I can' t use those which owner is 'ServerName\myUserName'
Most likely, the problem is that you are not fully qualifying the stored procedure name.exec [Domain\UserName].[procname]motexec [procname] CODO ERGO SUM |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-07 : 13:04:04
|
| Hello,And from which point of SQL Server Enterprise Manager I can change it? It would not be better change all stored procedures owner to ‘dbo’? The old SP’ s I created with my old computer has ‘dbo’ owner, but the new ones created with my new computer has ‘ServerName\myUserName’ owner. Indeed, I don’ t understand which is the advantage of use ‘ServerName\myUserName’ owner instead of another owner. This ‘new’ owner is added automatically every time I create a SP under ‘ServerName\myUserName’ account, and I think this happens because in my new computer I chose authentication with Windows Account instead of Sql server account. Which type of ‘owner’ you recommend me? My environment is a web server (Windows 2000 Server), with Sql Server 2000 as DB, that will be a production server running an ASP.NET VB.NET web app. Now I have only two users in Windows 2000 (ServerName\myUserName and ServerName\Administrator)Thank you |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-08 : 04:22:03
|
| Michael, I see your are right. If I call SP's in the way you said [Domain\UserName].[procname], then the SP works from Administrator Windows account. This means that I always have to call SP's from web app in that way? [Domain\UserName].[procname] Is this the normal way to deal with Stored Proc from app? |
|
|
|
anilkdanta
Starting Member
25 Posts |
Posted - 2006-09-08 : 08:54:29
|
| Hi,Please try doing this. 1) Create a user with role as sysadmin ( System Administrator )2) Verify the owner name against the SPs in Enterprise Manager. if it is showing any user name, Re-Create all your stored procedures after connecting to the server with user name created in step 1.3) Use the user name created in step 1 while connecting as"Initial Catalog='username' and you should create a Datasource Name with name same as 'username' on Server which you have deployed the web application.4) Call the Stored procedures "exec sp_name" no need of using owner.object or anything.Hope this solves your problem.Let me know the status. please.......Anil |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-08 : 11:34:33
|
| Such a new user I would create with role as sysadmin, should use Administrator account in Windows 2000 Server to start session in Sql Server? If so, what will happen when I will create new stored procedures from another Windows account? Because now the new SP’ s created appears with the Windows account as owner name.If you mean the new user I would create is only a user of Sql (without Windows authentication), what about Sql Server recommendation about using Windows authentication instead of only Sql user for security reasons? My database name has to be the same as my Sql sysadmin user name??.. The thing I don’ t understand is why when I create tables from SQL Server Enterprise Manager (in my Windows normal user account, not administrator) the table has ‘dbo’ as owner, which no problems exists when I run the app from Windows administrator account. But when I create my SP’ s from Query Analyzer, instead of ‘dbo’ appears my Sql user name of my current Windows account.. Which is when I have problems running the app from Windows administrator account |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-09-08 : 14:00:42
|
Ok, I see the logic. If my current Sql user (from which now I create SP’ s) would have sysadmin profile, the SP’ s owner would be ‘dbo’, and I could call them from any Windows account only using the name of the SP. I have tried it and works fine! So the only work now is (as you said) recreate all SP from my Sql user with new sysadmin rights, nothing else.. So, Anil your steps helped me to find this solution. Do you think it’ s enough? The only thing which I don’ t completely understand of your post is point 3:quote:
3) Use the user name created in step 1 while connecting as"Initial Catalog='username' and you should create a Datasource Name with name same as 'username' on Server which you have deployed the web application.
With ‘Datasource Name’ you mean my database name? With Initial Catalog='username' do you mean my connection configuration in my web app?Thank you! |
|
|
|
anilkdanta
Starting Member
25 Posts |
Posted - 2006-09-09 : 02:09:34
|
| Ok, let me put this clearly.As you have windows user accounts. If any of these windows users need to access any of the databases you have to give him access rights for that database by creating a login for this windows user.The username can be same as windows domain account or different. Ex: Windows account - MyDomain\cesarkLogin account for database 'XYZ' - cesark or cesark123 (no domain info here)Who ever is creating SPs, Tables and other objects should be of sysadmin role. By doing this you are making sure the owner is always 'dbo'. So, no issues with owner name qualifier for objects.Your database can with any name of your choice. And if you create SPs from EM or QA it is same.Now coming to my step3 of my earlier message. I mean to say if you are connecting to the database using a ODBC, then you have to create a DSN for the database 'XYZ' on the Server where you have deployed your Web Pages. You can have the database on the same machine or a different machine. The DSN name, and username used for the login for the database (cesark or cesark123) should be same.Now use this DSN name in the connection configuration for your web applicationn (Initial Catalog='DSN_Name' or 'username' as both are same). It seems you are using XML file for this. And nothing stops you from keeping your database name also same as DSN, username here. And LIFE BECOMES SMOOTH by doing like this.Hope the above explanation resolves your issue. |
|
|
|
|
My web app can’ t find Stored Procedures