DB Users Prefixed With Domain

Question

DB Users Prefixed With Domain

DSquared

Old Hand

Points: 347
More actions
February 8, 2012 at 1:41 pm

#268063

We are running SQL 2008 R2 instances with users in multiple domains in different forests. Call them DomainA and DomainB. This causes a lot of confusion (we're in the process of fixing it), so users are often explicit about prefixing user accounts with the proper domain. Users typically have logins through AD group membership, then have DB Users.
My question is, since a DB User is explicitly tied to an AD login, is there any reason to prefix the DB User with the domain? Is there an explicit reason that we would NOT want to do this (it matters when we have to strip the domain out of automated scripts)?
Thanks,
DSquared

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sumitagarwal781 Default port Points: 1493 More actions · Answer 1

DSquared (2/8/2012)
We are running SQL 2008 R2 instances with users in multiple domains in different forests. Call them DomainA and DomainB. This causes a lot of confusion (we're in the process of fixing it), so users are often explicit about prefixing user accounts with the proper domain. Users typically have logins through AD group membership, then have DB Users.
My question is, since a DB User is explicitly tied to an AD login, is there any reason to prefix the DB User with the domain? Is there an explicit reason that we would NOT want to do this (it matters when we have to strip the domain out of automated scripts)?
Thanks,
DSquared

Trusted login requires the domain prefix then it will get authenticated with AD ,unless it wont able to login to SQL server.

java

DSquared Old Hand Points: 347 More actions · Answer 2

In our case user logins are authenticated to the instance by group membership. They access the individual DB through the app using a DB User. At the DB level is where I'm interested in the domain prefixing.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok, i think i see what you mean.

correct me if I'm misunderstanding the question please.

The default behavior is to create a user witht eh same name as a login.

so for example, if i script my domain user like this:

CREATE LOGIN [mydomain\Lowell] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]

the question is, do i have to to this, or can i ignore the domain portion?

use WHATEVER;

CREATE USER [mydomain\Lowell] FOR LOGIN [mydomain\Lowell]

--Or is this OK?

CREATE USER [Lowell] FOR LOGIN [mydomain\Lowell]

First, will it affect anything...I don't think it makes a difference. the sid in sys.database_principals and the sid in sys.server_principals are what are used for the match the names are more for informational purposes.

The answer, for me, is I kind of like the consistency of being able to looka t a user, and if it has a slash, i KNOW it's a domain user and not a SQL Login that was created. for me, that has a bit of value when diagnosing issues.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DSquared Old Hand Points: 347 More actions · Answer 4

Lowell,

You've done a much better job than I did in explaining the issue - thanks!

I'm leaning towards leaving the domain in the DB User for now. Not so much to know that they are Windows users, but more to know where the user was originally added from. As we migrate to the final domain, I would like to see who is still access from the old, or at least thinks they're accessing from the old.

Thanks,

D