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 |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2010-12-29 : 12:03:17
|
| I (DBA) have an application database who's db_owner user does not have a login related to it. The application guys say thisis the id that the application uses for access. The applicationuses IIS (I'm profoundly ignorant in this realm). So how does the data get accessed? Does this have something to do with endpoints? Where does authentication happen? Do you have some queries on system tables/views that can help me understandwho has what access to this database? If it is an endpoint thing,Books Online won't be much help. BOL articles in this area aren't written for the clueless. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-12-29 : 16:55:19
|
| SQL Server uses an Authenticate and Authorize approach to security.The first step in allowing an external entity, in your case an IIS Application, get to the database goodies is to Login to the SQL Server. If you can't do that, then you're dead in the water. So the first question is, how is the IIS Application trying to Login to the server? Is it as a Windows User, Windows Group or SQL User? The second question is, does that Login exist at the SQL Server (not database) level? Within the SQL Management Studio, under the Object Explorer, there is a Security sub-tree. Under this is a Logins section that lists your known Logins (well, duh!). You may need to refresh the list by highlighting the sub-tree heading and hitting F5.The Login allows SQL Server to Authenticate, which just means that it can determine WHO you are; not what can you do. Once it knows who you are, it can map a Login to a User within a database. Note that one Login can map to a variety of databases. Note also, that the Login name and the User name are, by default, the same but that they do not need to be. If you right click the Login name, you can go to the Server Roles section to see what roles, if any, it is a member of. It will always be a member of Public. A lazy DBA might make it part of sysadmin, too. When you go to the User Mapping section, you'll see what databases the Login has been mapped to for each database, what the User name is and what Database Roles it might be a member of. If your Login does not have a mapping to your database, you need to fix that first.The User gets Authorization by being a member of a Server Role, Database Role or has rights explicitly Granted to it. It sounds like your User is supposed to be a member of the db_owner Database Role. If it's not; you need to fix that.If you are still having issues, give us the details and we'll see if we can ferret out the solution.=======================================Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986) |
 |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2010-12-30 : 13:41:48
|
It sure looks to me that there is something else going on here.Everything you said sounds just like I expect things to work.Fact remains that the id that the application uses is a database user and IS NOT tied to a SQL Server login. There is, however, a Windows user with the same name, which is not a member of any groups, but which seems to be used to "run the aspnet worker process."The user in question is named aspnet. It is not listed in the instance login list.The example is QA but prod is set up the same way.It looks like I can't attach any doc. I'm gonna try to insert some pictures.     |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-12-30 : 14:31:48
|
| It is possible to get into this situation, for instance when restoring a database to a different server. I think that you could also explicitly create a User without a Login but I've never felt the need for this option. The solution is to create the appropriate Login at the Server level, go to the desired database(s) and use the ALTER USER statement to map the User to the Login (ALTER USER aspnet with LOGIN = ???).=======================================Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986) |
|
|
|
|
|
|
|
|
db_owner has no login - endpoints??