|
Home > Archive > MS SQL Server > March 2006 > how should users' domain be changed?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
how should users' domain be changed?
|
|
|
| Hello everyone,
Right now, our SQL 2000 server has both local and domain users. Domain
user's login is something like 'olddomain\someuser'
. There are quite a few
domain users. Now we need to move the SQL server to a new domain. The
question is how to change their logins to 'newdomain\someuser'
in a mass
mode? What about those domain users listed under each database?
Thanks in advance for any ideas.
Bing
| |
| mulhall 2006-02-25, 9:23 am |
| Have you heard of using groups? Now you know why.
If yuor users are staying in olddomain, you will need a trust relationship
created so that newdomain trusts oldomain accounts. If this is in place,
nothing on your SL server will need to be changed.
"bing" wrote:
> Hello everyone,
>
> Right now, our SQL 2000 server has both local and domain users. Domain
> user's login is something like 'olddomain\someuser'
. There are quite a few
> domain users. Now we need to move the SQL server to a new domain. The
> question is how to change their logins to 'newdomain\someuser'
in a mass
> mode? What about those domain users listed under each database?
>
> Thanks in advance for any ideas.
>
> Bing
| |
| John Bell 2006-02-25, 9:23 am |
| Hi
You should be able to modify the script at
http://support.microsoft.com/kb/246133/ to do this.
John
"bing" wrote:
> Hello everyone,
>
> Right now, our SQL 2000 server has both local and domain users. Domain
> user's login is something like 'olddomain\someuser'
. There are quite a few
> domain users. Now we need to move the SQL server to a new domain. The
> question is how to change their logins to 'newdomain\someuser'
in a mass
> mode? What about those domain users listed under each database?
>
> Thanks in advance for any ideas.
>
> Bing
| |
|
|
"mulhall" wrote:
> Have you heard of using groups? Now you know why.
>
Oh, yeah, I've heard of it zillions of times. But I don't know why those
standalone logins were not in any groups when they were added in the first
place. I'm trying to fix the problem that have caused.
>
> If yuor users are staying in olddomain, you will need a trust relationship
> created so that newdomain trusts oldomain accounts. If this is in place,
> nothing on your SL server will need to be changed.
>
Unfortunately, they are not going to stay in the olddomain. Actually every
user is in both the olddomain (olddomain\someuser)
and the newdomain
(newdomain\someuser)
right now. The olddomain will be gone eventually. Our
SQL server is currently in the olddomain. So I thought renaming
'olddomain\someuser'
to 'newdomain\someuser'
would be the only thing we need
to do, hopefully.
Thanks for the help,
Bing
[color=darkred]
> "bing" wrote:
>
| |
|
| Thanks very much for the pointer. I'll give it a try.
Bing
"John Bell" wrote:
[color=darkred]
> Hi
>
> You should be able to modify the script at
> http://support.microsoft.com/kb/246133/ to do this.
>
> John
>
> "bing" wrote:
>
| |
|
| "John Bell" wrote:
> Hi
>
> You should be able to modify the script at
> http://support.microsoft.com/kb/246133/ to do this.
I run the script that article provided. The result was 'The command(s)
completed successfully'. But I did not see sp_hexadecimal and
sp_help_revlogin generated as stored procedures in the master database.
Bing
| |
| John Bell 2006-03-05, 8:23 pm |
| Hi
Have you tried to run it?
EXEC master..sp_help_revlogin
If you haven't got the no execute option on, then I am not sure why it does
not create it! You did include the USE statement and I presume you have
permissions to create this procedure?
John
"bing" wrote:
> "John Bell" wrote:
>
>
> I run the script that article provided. The result was 'The command(s)
> completed successfully'. But I did not see sp_hexadecimal and
> sp_help_revlogin generated as stored procedures in the master database.
>
> Bing
| |
|
| "bing" wrote:
> "John Bell" wrote:
>
>
> I run the script that article provided. The result was 'The command(s)
> completed successfully'. But I did not see sp_hexadecimal and
> sp_help_revlogin generated as stored procedures in the master database.
>
> Bing
Never mind. I closed the EM and reopened it again. Now they show up.
Bing
| |
|
| Hi,
Yes, I tried. sp_help_revlogin generated a script fine. What I need is to
change every login's domain name. In the script generated by sp_revlogin,
it's like:
EXEC master..sp_grantlogin 'olddomain\user' for all the logins. We need
them to be "EXEC master..sp_grantlogin 'newdomain\user'".
So the question is in the original script, how should I replace the old
domain name in @name with the new domain name we're going to migrate to.
SET @tmpstr = 'EXEC master..sp_grantlogin '''+ @name + ''''
I'm not familiar with this scripting language and its syntax. So I'd
greatly appreciate any help with that.
Thanks,
Bing
"John Bell" wrote:
[color=darkred]
> Hi
>
> Have you tried to run it?
>
> EXEC master..sp_help_revlogin
>
> If you haven't got the no execute option on, then I am not sure why it does
> not create it! You did include the USE statement and I presume you have
> permissions to create this procedure?
>
> John
>
>
> "bing" wrote:
>
| |
|
| Antoher question. I've used sp_help_revlogin and sp_hexadecimal to replace
the olddomain name with the newdomain for each login fine. Now each user or
group has both 'olddomain\user' (or 'olddomain\group') and 'newdomain\user'
(or 'newdomain\group'). Eventually, we're going to delete all the olddomain
logins and users.
Now the question is, how should I create 'newdomain\user' for each
'olddomain\user' in each database and make sure this 'newdomain\user' has the
exactly same permissions as olddomain\user?
I'd greatly appreciate any ideas or pointers.
Thanks,
Bing
"bing" wrote:
[color=darkred]
> Hi,
>
> Yes, I tried. sp_help_revlogin generated a script fine. What I need is to
> change every login's domain name. In the script generated by sp_revlogin,
> it's like:
> EXEC master..sp_grantlogin 'olddomain\user' for all the logins. We need
> them to be "EXEC master..sp_grantlogin 'newdomain\user'".
>
> So the question is in the original script, how should I replace the old
> domain name in @name with the new domain name we're going to migrate to.
>
> SET @tmpstr = 'EXEC master..sp_grantlogin '''+ @name + ''''
>
> I'm not familiar with this scripting language and its syntax. So I'd
> greatly appreciate any help with that.
>
> Thanks,
>
> Bing
>
> "John Bell" wrote:
>
| |
| John Bell 2006-03-05, 8:24 pm |
| Hi
You should really use groups to do this. If you use windows groups and/or
database roles the individual user only needs to be added to the specific
group/role and the permissions are governed by which groups/roles that user
is in. If you use windows groups you can deligate the resonsibility for
allocation a users permissions to the system administrators. Using both
windows groups and database roles will give you fuller flexibility.
Therefore I would do the following:
1. Create one or more roles in the database
2. Allocate the permissions to that role(s)
3. Get the System Admin to create a security group(s) for database access
4. Get the System Admin to add the specified users to that group
5. Grant login to a windows group(s)
6. Add the group(s) to the role(s) you have created
Change your bring on documentation to make sure that the system admin adds
new loggings to the correct groups.
John
"bing" wrote:
[color=darkred]
> Antoher question. I've used sp_help_revlogin and sp_hexadecimal to replace
> the olddomain name with the newdomain for each login fine. Now each user or
> group has both 'olddomain\user' (or 'olddomain\group') and 'newdomain\user'
> (or 'newdomain\group'). Eventually, we're going to delete all the olddomain
> logins and users.
>
> Now the question is, how should I create 'newdomain\user' for each
> 'olddomain\user' in each database and make sure this 'newdomain\user' has the
> exactly same permissions as olddomain\user?
>
> I'd greatly appreciate any ideas or pointers.
>
> Thanks,
>
> Bing
>
> "bing" wrote:
>
| |
|
| Thanks, John, for your advise. I really appreciated it. That's should be
the way to go. But one thing I'm not very clear is even if groups are used,
we're still facing the same problem as how to rename those groups in the
olddomain when the SQL server is transfered to a new domain. For instance,
say, given the following situation, we need to create 'newdomain\group1'
with the same permissions for 'olddomain\group1'. Same thing for other
groups. We have quite a bunch of databases. So it's tedious to do it
manually. How should I do that?
database1
users
olddomain\group1
olddomain\group2
database2
users
olddomain\group3
database3
....
Bing
"John Bell" wrote:
[color=darkred]
> Hi
>
> You should really use groups to do this. If you use windows groups and/or
> database roles the individual user only needs to be added to the specific
> group/role and the permissions are governed by which groups/roles that user
> is in. If you use windows groups you can deligate the resonsibility for
> allocation a users permissions to the system administrators. Using both
> windows groups and database roles will give you fuller flexibility.
>
> Therefore I would do the following:
>
> 1. Create one or more roles in the database
>
> 2. Allocate the permissions to that role(s)
>
> 3. Get the System Admin to create a security group(s) for database access
>
> 4. Get the System Admin to add the specified users to that group
>
> 5. Grant login to a windows group(s)
>
> 6. Add the group(s) to the role(s) you have created
>
> Change your bring on documentation to make sure that the system admin adds
> new loggings to the correct groups.
>
>
> John
>
>
> "bing" wrote:
>
| |
| John Bell 2006-03-05, 8:24 pm |
| Hi
If you are using SQL Server roles, then it would only be a relatively small
change possibly one Windows group per SQL server role, and that would be
quite easy to do manually. You would therefore have to get the roles set up
before the migration.
To set up the roles, if you have a large number of database, you may wish to
use DMO to script out the permissions, you could then modify the script to
grant permissions to the role, this would be easy if you can use a specific
user as a template. If you only use stored procedures the the number of
objects that need granting permissions would be limited and you could
possibly use the scripting options in Enterprise Manager to do this (uncheck
everything except the permissions on the the options tab!)
It sounds like you are not using a source code control system to hold your
database object. If you were then these scripts would already be available to
you, you may want to consider doing this to bring your development into a
more manageable and accountable envrionment.
John
"bing" wrote:
[color=darkred]
> Thanks, John, for your advise. I really appreciated it. That's should be
> the way to go. But one thing I'm not very clear is even if groups are used,
> we're still facing the same problem as how to rename those groups in the
> olddomain when the SQL server is transfered to a new domain. For instance,
> say, given the following situation, we need to create 'newdomain\group1'
> with the same permissions for 'olddomain\group1'. Same thing for other
> groups. We have quite a bunch of databases. So it's tedious to do it
> manually. How should I do that?
>
> database1
> users
> olddomain\group1
> olddomain\group2
> database2
> users
> olddomain\group3
> database3
> ....
>
> Bing
>
> "John Bell" wrote:
>
| |
|
| "John Bell" wrote:
[snip]
> To set up the roles, if you have a large number of database, you may wish to
> use DMO to script out the permissions, you could then modify the script to
> grant permissions to the role, this would be easy if you can use a specific
> user as a template. If you only use stored procedures the the number of
> objects that need granting permissions would be limited and you could
> possibly use the scripting options in Enterprise Manager to do this (uncheck
> everything except the permissions on the the options tab!)
"Scripting options" sounds vaguely familiar. Which options tab is that in EM?
>
> It sounds like you are not using a source code control system to hold your
> database object. If you were then these scripts would already be available to
> you, you may want to consider doing this to bring your development into a
> more manageable and accountable envrionment.
>
No, we're not using any source code control systems. It's new to me. I'll
take a look.
THANKS!
Bing
| |
| John Bell 2006-03-05, 8:24 pm |
| Hi
Ken Henderson inhis book Guru's Guide to SQL Server, Stored Procedure, XML
and HTML ISDN 0201700468 has a chapter on why you should use source code
control, which would be worthwhile reading. If you treat your database
objects as any other piece of source code and place the definitions in
source code control, changes can then be made accountable and controlled.
You will can track down when and why things have changed which is especially
useful when things get broken! It will also give you the ability to build
any version at any time if you combine it with your release procedures.
This may give you a start for scripting the database,
http://www.nigelrivett.net/ DMOScri...se
s.html but you may want to
look up the example in books online if you wish to call it from another
programming language.
Scripting options in Enterprise Manager are by right clicking on the tree
view and choosing the Generate SQL option which is sometimes in the All
tasks menu.
John
"bing" <bing@discussions.microsoft.com> wrote in message
news:227440D1-05B8-4F66-8CA9- C4FFC7F92E7E@microso
ft.com...
> "John Bell" wrote:
>
> [snip]
>
>
> "Scripting options" sounds vaguely familiar. Which options tab is that in
> EM?
>
>
> No, we're not using any source code control systems. It's new to me.
> I'll
> take a look.
>
> THANKS!
>
> Bing
|
|
|
|
|