| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2004-10-04 : 13:56:50
|
| I've got a SQL user named jdoe that has read permissions on DB1. I'm switching from SQL permissions to Windows permissions for reasons I won't get into. I've added the DOMAIN1\jdoe user to Security/Logins, and when I try to give this user permissions on DB1, I get "Error 21002:[SQL-DMO]User 'jdoe' already exists." It appears that SQL is confusing DOMAIN1\jdoe for the SQL user jdoe. If I remove jdoe from DB1, then I can add DOMAIN1\jdoe, but this isn't acceptable because I need both to have read permissions during the transition. What do I do? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-04 : 14:03:03
|
| When you grant access to the Windows account, have the user be DomainName\UserName. So when you run sp_grantdbaccess, do this:EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Corporate\GeorgeW'instead of this:EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'GeorgeW'Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-04 : 14:03:16
|
| You can map DOMAIN1\jdoe to a different user in the database e.g. DOMAIN1\jdoe.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-10-04 : 14:08:47
|
| It works in the Logins area in EM if I add the "DOMAIN1\" to the front after I put a check next to the database name. That's kind of annoying, but not a huge deal. How do I know if DOMAIN1\jdoe is "mapped" to jdoe? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-04 : 14:10:41
|
| If you use EM and don't change what it does, then it'll give jdoe as the username inside the database which is causing your problem. Yes you'll need to either select a different username for use inside the database or add the domain name like I've got in the example code in my post. How do you know what it is mapped to, well from within EM, double click the login, go to Database Access tab. It'll show under the User column.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-10-04 : 14:16:07
|
| So you're saying that all users will automatically be mapped from DOMAIN\USER to USER when I add them as a new login? That seems dumb on Microsoft's part. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-04 : 14:18:19
|
| If you don't change the values inside EM, then yes. But that's up to you to make the change. There's no reason for MS to know that you want a different userid in the database, so how is that dumb? And why do this inside EM? The code is rather short for QA.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-10-04 : 14:25:01
|
| It's still quicker for me in EM.It seems to me that if you build a database so that people can use either Windows perms or SQL or both separately, you should make it so that they are automatically separate but can be mapped to each other if desired, instead of the other way around. Maybe if I came from a much larger environment I would understand your view. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-04 : 14:27:04
|
| That's true, but how does EM know that's what you intend? How does it know that jdoe isn't what you want inside the database instead of Domain1\jdoe? That's why EM allows you to make the change while you are adding the login.Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-04 : 23:36:42
|
actually the subject of discussion is just an alias.each user may be granted a standard login and/or windows login. now if you have user jdoe, then you can have standard login: jdoe and/or windows: domain1\jdoe. so which account are you going to map the alias jdoe? on standard or windows?just a thought... |
|
|
|
|
Windows vs. SQL, user already exists