Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bryanc
Starting Member
6 Posts |
 Posted - 2006-06-20 : 00:24:01
|
| hey all, i have a weird authentication error.i have two servers, both running sql server 2005. one is on my workstation the other is on my dev server. i backed up my workstation database and restored it on the dev server. my workstation is xp and my server win2k3. i'm developing an asp.net 2.0 app that talks to my sql server using windows auth. on my workstation my website runs under the ASPNET account, IUSER_COMPUTERNAME and i had that account setup in sql on my workstation in the main security folder, then again in the db security folder the name of it was [ASPNET LOGIN]. each sproc had EXEC permission for [ASPNET LOGIN].when i restored it over to my win2k3 box i deleted that login from the database and revoked it on all the sprocs. i then setup another account for the application and set my website to use that account. i couldn't use the original account because of the group it was in, blah blah blah, not important. the thing is, i got rid of all instances of that ASPNET LOGIN account and i'm running the website under a new account, and some of the stored procedures work, but some of them i get the error "Cannot find the user 'ASPNET LOGIN', because it does not exist or you do not have permission." which is crazy, because i've gotten rid of all references to that. it's not anywhere i see in the database, not in either security/users folder, it's not on any of the stored procedures. and it's NOWHERE in the website. not in IIS or code. furthermore some of the stored procedures work. as far as i can tell, the ones that are not called from my custom MemberShipProvider are working. so i went back to my database on my workstation, revoked exec permission on the sprocs, deleted the login from the db and then from the sql server instance, backed it up again, restored on my server and i'm still getting this issue.where can that ASPNET LOGIN be hiding? i restarted too thinking maybe it was cached somewhere. this is my connection string that the entire app uses: "Server=[serverName]; Database=[dbName]; Trusted_Connection=true;"my web.config is using <identity impersonate="true"/> and my website is running under the account i created.any ideas? i've been battling this for half a day. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-20 : 00:42:42
|
| "where can that ASPNET LOGIN be hiding?"Have you got code which directly references the object owner?For example select * from [ASPNET LOGIN].mytableKristen |
 |
|
|
bryanc
Starting Member
6 Posts |
Posted - 2006-06-20 : 00:45:18
|
| nope, nowhere. i did a solution wide search for it. 0 hits. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-20 : 00:47:54
|
| "i did a solution wide search for it"Including within the database for any Triggers, Sprocs, Views etc.?Just checking!Kristen |
|
|
|
bryanc
Starting Member
6 Posts |
Posted - 2006-06-20 : 00:52:29
|
| there aren't any triggers, views, etc. all sprocs. |
|
|
|
bryanc
Starting Member
6 Posts |
Posted - 2006-06-20 : 00:54:49
|
| i went through and got rid of that account on my workstation too. i didn't delete the account from the system, but i revoked it on all the sprocs, changed my website over to run under a different account, deleted it from the db and the sql instance and now i'm getting the same thing. so somwhere it's being cached. and i'm pretty sure it's in SQL server, since i searched my entire codebase in vs.net and can't find a reference to it. |
|
|
|
bryanc
Starting Member
6 Posts |
Posted - 2006-06-20 : 01:11:54
|
| anyone have a sql script to search all objects in a db for a string? |
|
|
|
bryanc
Starting Member
6 Posts |
Posted - 2006-06-20 : 01:21:04
|
| i fixed it. i narrowed it down to one stored procedure: sp_GetOneUserByUserName. i dropped it, recreated it, and applied permissions to it on my workstation, fixed. backed it up, restored onto my server, no workey. so i dropped it, recreated, and re-applied permissions on my server. fixed. the only guess i have is that somehow it created in another database or something and i was missing it.the really weird thing is that when i ran this: declare @UserName as nvarchar(256) set @UserName = '[IUSR_METIS]' select 'grant execute on '+a.name+ ' to ' + @UserName from sysobjects a ,sysprotects b where a.type in ('P','Fn') --and user_name(b.uid) <> 'public' and a.name not like 'dt_%' and a.id=b.id and action=224 order by a.name ASCto get my grant execs that one sproc didn't show up so i had to manually add it, but it would show up if i ran this: declare @UserName as nvarchar(256) set @UserName = '[ActorsPad_WebUser]' select 'grant execute on '+a.name+ ' to ' + @UserName from sysobjects a --,sysprotects b where a.type in ('P','Fn') order by a.name ASCi don't remember enough anymore about the sysobjects to know why it wasn't showing up, but it's fixed now.-b |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-20 : 17:00:40
|
| "i don't remember enough anymore about the sysobjects to know why it wasn't showing up"Unless your [i.e. any user] login is aliased to DBO then objects you make will be created in your name, not dbo.So someone created an object and it happens that it was created in their name ...Basically an accident waiting to happen!Kriste |
|
|
|
|
|
|
|
|
weird user login error