Domain account change

Question

Hi All,

We have Domain account say 'xyz\rbprodsql' under sql security which we are planning to migrate to a new domain say 'ABC\rbprodsql' and disable the old domain temporarily and drop them later once we are fine with modified named.

Question:

Do we have any script which grabs all permission from old domain account and create a new domain with same permissions?

Thanks.

Nani.

---

Thanks, Nani.

Answer 1

Hi All,

We have Domain account say 'xyz\rbprodsql' under sql security which we are planning to migrate to a new domain say 'ABC\rbprodsql' and disable the old domain temporarily and drop them later once we are fine with modified named.

Question:

Do we have any script which grabs all permission from old domain account and create a new domain with same permissions?

Thanks.

Nani.

---

Thanks, Nani.

Answer 2

If you are trying to script out all the permissions I would try this script.  (Adapted from here: http://www.sqlservercentral.com/scripts/Security/62477/  ) Couple size changes so it would work.

Then with the results you could search and replace the domain name part of the grant statements

use master
go
begin
declare @databasename varchar(300)
declare cur cursor for 
select name from sysdatabases
create table #result
(dbname varchar(300),result varchar(300))
open cur
fetch next from cur into @databasename
while(@@fetch_status=0)
begin
create table #t 
(a1 varchar(500)
,a2 varchar(500)
,a3 varchar(500)
,a4 varchar(500)
,a5 varchar(500)
,a6 varchar(500)
,a7 varchar(500))
insert into #t exec sp_helprotect @username = null
insert into #result select @databasename,a5+' '+a6+' on ['+a1+'].['+a2+']'+
CASE
WHEN (PATINDEX('%All%', a7)=0) and (a7 <> '.') 
 THEN ' ('+a7+')'
ELSE ''
END+' to ['+a3+']' from #t
drop table #t
fetch next from cur into @databasename
end
select * from #result
close cur
deallocate cur
drop table #result
end
go

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 3

One more thought on this.  This would be a good time to clean up your security model.  If you create local groups and assign global AD groups or AD accounts to the local groups, then you can assign your SQL permissions to the groups.  This can really simplify administration since you never have to explicitly give permissions to an AD account in sql server.

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 4

hello Nani,

first you need to know that a domain account is mapped to a SQL login which is itself mapped to database users.

All database permission (resp. database roles) are applied to the database user and not to the login. Only SQL Server server roles are applied to the SQL login but those are mainly restricted to mangement of the SQL server and there are only about a few roles which can be listened in SSMS or by a predefined stored procedure.

assuming that the domain account is a normal database user, you can just switch the login for the database user and you're done.

you can use either sp_change_users_login http://technet.microsoft.com/en-us/library/ms174378.aspx   or ALTER USER statement http://technet.microsoft.com/en-us/library/ms176060.aspx

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

why posting twice this question?

I've answered your question in the other thread http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/06f553ab-a553-4fc3-bdf3-1d48b1e76292

hello Nani,

first you need to know that a domain account is mapped to a SQL login which is itself mapped to database users.

All database permission (resp. database roles) are applied to the database user and not to the login. Only SQL Server server roles are applied to the SQL login but those are mainly restricted to mangement of the SQL server and there are only about a few roles which can be listened in SSMS or by a predefined stored procedure.

assuming that the domain account is a normal database user, you can just switch the login for the database user and you're done.

you can use either sp_change_users_login http://technet.microsoft.com/en-us/library/ms174378.aspx   or ALTER USER statement http://technet.microsoft.com/en-us/library/ms176060.aspx

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.