February 12, 2009 at 8:40 am
HI all... recently I noticed something regarding users and logins....
Up to now I knew that a user of a particular database must have login created at instance level...
I noticed recently that if i create a user directly at database level...and i specify, in the field called login name, DOMAIN\USERNAME....then the user get created normally....bypassing the SQL SERVER LOGIN ACCOUNT....can anybody say if this is a good thing or a bad thing???
February 12, 2009 at 8:44 am
I'm not clear on what you're running into. Are you saying that logins don't exist at the server level but do exist at the database level? If so, they have to be mapped to a user/group at the server level.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 9:24 am
Hi there....basically the user get created at database level like DOMAIN\USERNAME....and there is no LOGIN/GROUP created at Server(instance) Level....I know that is sound strange....at the beginning i was thinking that this user was a member of a group created at Server(instance) Level...but i could not find any group or login....so to test the situation this is what i have done...
1) created a local instance in my PC and I made the services to be start with a domain account like DOMAIN\SQLSERVERSERVICEUSER...so this way the instance is visible to the network.
2) I opened the ADVENTUREWORKS Database, i went to USERS, right click, create a new user....and I gave the name like DOMAIN\USERNAME....and magic...that user have access to the database....AND THERE IS NO LOGIN OR GROUP at the Server(instance) level....
I always thought that you need a login created at Server(instance) level to grant access to the instance first....
Is the situation clear now...??? does anybody experienced the same??? Do you see the feature as good or bad?? I even tryed with SQL 2000....and it works the same way as SQL 2005....
February 12, 2009 at 9:56 am
That adds a user, but doesn't allow a login to the server, because there's no login defined on a server level.
Here's what I did: Added an Active Directory account to users of AW. Specified none-existant account, got an error message. Specified an existing account, let's call it "domain\someuser", user was created with no problems. Now, the AD login I specified, does not have access to the SQL server and is not in any of the groups that would have access. Started SSMS from under the "domain\someuser", but when I tried to log in into the server, I got the error message:
"Login failed for user 'domain\someuser'. (Microsoft SQL Server, Error: 18456)"
So, it looks like the user you are adding is still an orphaned user without a login.
February 12, 2009 at 9:57 am
Maybe what you're experiencing is the ability in SQL 2005 to create a user without mapping it to a login. See CREATE USER in BOL and this article: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.
I am a bit confused by your statement that the same thing happens in SQL 2000. I thought the feature wasn't available before SQL 2005.
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply