August 11, 2008 at 6:13 am
Hi,
I work ofr a software company, our software uses DSN connections to SQL Server databases using ODBC sources from application servers.
The databases have users set up on them who are the owners of the objects (or the schema is the owner) and these users have specific roles assigned instead of dbo owning the objects.
We have been using SQL Server 2000, the most recent version of our software uses 2005.
I'm perfectly happy with setting this stuff up - add a new login and it creates the user, schema etc on the default database I set.
What I don't understand is the relationship between the server login and the database user/schema and I need to explain it in a training course.
So when a connection string is used, for example -
DSN=MAINDB;UID=MAINDB;PWD=PASSWORD;
what happens? I presume these details are passed through the server login and then database user as well to connect to the database? If o why do you need the server login and the database user?
August 11, 2008 at 6:22 am
The login is a server-level principle. All that gets you is access into the server and (possibly) rights to server-level objects and permissions (logins, linked servers, jobs)
The database user links the login into specific databases. It means that someone who logs into the server doesn't automatically have access to all databases. It's a double level so that you can have databases that are only accessible for different users (eg logins from people in the HR dept can't access the Sales database and vis versa)
The server login is the one that has a password (or is linked to Active directory in the case of windows authentication). Database users are linked to a login, and have no password listed.
Does that help at all?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2008 at 6:30 am
Yes it does, thanks. So the users don't have passwords but you have to login using a login to get access in the first place to the server.
August 11, 2008 at 6:38 am
Yup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply