February 12, 2020 at 12:17 pm
Morning all,
My AD team been moved group of users from one domain to other domain. ex: abc\SQL1 to xyz\SQL1.
One of the application is connecting and having permission issue. Is there anything to fix without adding new group xyz\SQL1.
Even, we tested by adding xyz\SQL1, this it is not completely working some SSRS reports having permission issue.
Any recommendation, hope some have already done this kind of migration. Since, my team is planning to move all users to new domain.
February 12, 2020 at 12:25 pm
I think you're going to have to put the work in, I'm afraid. Although the two accounts have the same name within their respective domains, SQL Server doesn't care about that; it uses the SID to identify the account. You'll need to add the new account with the same permissions and role memberships as the existing. If you have a lot of accounts like this (or even if this is the only one) you should script all that out - the more databases the account has access to, the more time and potential mistakes this will save you.
John
February 12, 2020 at 12:40 pm
Why not rename the logins / logingroup with ALTER LOGIN ... to keep the same SID ?
February 12, 2020 at 12:50 pm
Why not rename the logins / logingroup with ALTER LOGIN ... to keep the same SID ?
They're domain logins... you can't rename them with SQL Server. For Windows logins, SQL Server doesn't assign a SID, like it does for SQL logins - it takes it from Windows. Therefore you can't keep the same SID.
John
February 12, 2020 at 3:14 pm
Thanks John. Do you mean take a login scripts and keep it in some place.
But, still we have both account and domains in place. The both domain accounts been exists in the SQL server.
Error from ssrs : the permissions granted to user xyz\username are insufficient for performing this operation.
February 12, 2020 at 3:40 pm
By all means keep it somewhere safe, although you'll probably only need to use it the once.
-- Create logins
SELECT 'CREATE LOGIN [' + REPLACE(name,'abc','xyz') + '] FROM WINDOWS;'
FROM sys.server_principals
WHERE name LIKE 'abc\%'
AND type_desc LIKE 'WINDOWS_%';
-- Server-level permissions
SELECT
CASE
WHEN state IN ('G','W') THEN 'GRANT'
ELSE state_desc
END + ' ' + permission_name + ' TO [' + REPLACE(SUSER_NAME(grantee_principal_id),'abc','xyz') + '] ' +
CASE state
WHEN 'W' THEN 'WITH GRANT OPTION'
ELSE ''
END + ';'
FROM sys.server_permissions
WHERE state in ('G','S','W')
AND SUSER_NAME(grantee_principal_id) LIKE 'abc\%';
-- Server role memberships
SELECT 'ALTER ROLE [' + r.name + '] ADD MEMBER [' + REPLACE(l.name,'abc','xyz') + '];'
FROM sys.server_role_members m
JOIN sys.server_principals l ON m.member_principal_id = l.principal_id
JOIN sys.server_principals r ON m.role_principal_id = r.principal_id
WHERE l.name LIKE 'abc\%'
AND l.type_desc LIKE 'WINDOWS_%';
If the logins for the new domain already exist on the server then you don't need to run the first query. The above queries only provide the statements for server logins, roles and permissions: you will need to do the same thing at database level.
John
February 12, 2020 at 3:59 pm
Thanks for your help John.
I will take a look on the script.
February 12, 2020 at 4:11 pm
Forgot to add those are AD group and it has many members / users. I understand that, we need add new AD group in SQL with same permission of of old. (Still, i have not removed the old AD group from SQL)
February 12, 2020 at 4:16 pm
It's exactly the same principle for groups as for users. My script will create both for you.
John
February 12, 2020 at 10:03 pm
It looks like John's script only handles server level permissions, I think you'd need something like this to copy the database level permissions and roles for these users:
DECLARE
@orig_user sysname = N'DOM1\user',
@new_user sysname = N'DOM2\user';
SELECT
CASE p.class_desc WHEN 'OBJECT_OR_COLUMN' THEN
CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON ', QUOTENAME(SCHEMA_NAME(o.schema_id)), '.', QUOTENAME(o.name), ' TO ', QUOTENAME(@new_user), ' AS ', QUOTENAME(g.name), ';')
WHEN 'SCHEMA' THEN
CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON SCHEMA::', QUOTENAME(s.name), ' TO ', QUOTENAME(@new_user), ' AS ', QUOTENAME(g.name), ';')
WHEN 'DATABASE_PRINCIPAL' THEN
CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON USER::', QUOTENAME(i.name), ' TO ', QUOTENAME(@new_user), ' AS ', QUOTENAME(g.name), ';')
WHEN 'TYPE' THEN
CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON TYPE::', QUOTENAME(t.name), ' TO ', QUOTENAME(@new_user), ' AS ', QUOTENAME(g.name), ';')
ELSE
CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' TO ', QUOTENAME(@new_user), ' AS ', QUOTENAME(g.name), ';')
END AS sqlcmd
FROM sys.database_principals u
LEFT OUTER JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT OUTER JOIN sys.objects o ON p.major_id = o.object_id AND p.class_desc = 'OBJECT_OR_COLUMN'
LEFT OUTER JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class_desc = 'SCHEMA'
LEFT OUTER JOIN sys.database_principals i ON p.major_id = i.principal_id
LEFT OUTER JOIN sys.types t ON p.major_id = t.user_type_id
LEFT OUTER JOIN sys.database_principals g ON p.grantor_principal_id = g.principal_id
WHERE u.name = @orig_user
UNION ALL
SELECT CONCAT('ALTER ROLE ', QUOTENAME(r.name), ' ADD MEMBER ', QUOTENAME(@orig_user)) AS sqlcmd
FROM sys.database_principals u
INNER JOIN sys.database_role_members rm ON rm.member_principal_id = u.principal_id
INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE u.name = @orig_user;
February 13, 2020 at 10:32 am
It's exactly the same principle for groups as for users. My script will create both for you.
John
Thanks John. I have heard from AD team that if we keep the SID history in the AD level, no need to add do anything in the application side or SQL side (Like adding new domain account into SQL and match permission etc). I am not sure on that.
February 13, 2020 at 10:51 am
Mmmm... if your domain admin can create a XYZ\MyAccount with the same SID as ABC\MyAccount, then that might just work. I'm not a domain admin myself, so I don't know whether you can specify the SID at account creation, and even if you can, I'm not sure what would happen in SQL Server. One thing's for sure - it would lead to a bit of confusion, since you'd have Windows logins in SQL Server where the name of the login doesn't match the name of the Windows account! For your sanity, you might be better off investing the time to do it cleanly.
John
February 13, 2020 at 11:12 am
Thanks John. Got your point, that the name will not match it.
I have script out all logins and users, working with team.
February 13, 2020 at 11:13 am
Thanks Chris for script.
February 13, 2020 at 11:47 am
Msg 468, Level 16, State 9, Line 49
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" in the concat operation.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply