June 29, 2016 at 2:37 am
Hello everyone,
A couple of days ago I was tasked to do a P2V migration from let's say A to B...everything looked fine post migration. I took all the possible care to migrate the user permissions off of A to B, and as of now people seemed to have started complaining about access issues. I did compared apples with apples, but I did not see even a slight difference in the permissions on A, and B. However, it was strange to understand that a domain admin group that held sysadmin permissions on A, who also have the same sysadmin role on B, can access A, but they are facing issues with B...and I am like totally clueless on what else could go wrong.
I've even went to the extent of running, and finding out what members are part of that domain group, but all of them are part of it, and by running the query below. I can't see any issues from the SQL side.
exec master..xp_loginfo
@acctname = 'domain group'
@option = 'members'
I've also attached the error screenshots...any help or direction on this would be highly appreciated.
June 29, 2016 at 6:57 am
If you run this script, do the results look like you expect?
DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
INSERT @DB_USers
EXEC sp_MSforeachdb
'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
SELECT
dbname,username ,logintype ,create_date ,modify_date ,
STUFF(
(
SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH('')
)
,1,1,'') AS Permissions_user
FROM @DB_USers user1
GROUP BY
dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username
June 29, 2016 at 7:45 am
ffarouqi (6/29/2016)
Hello everyone,A couple of days ago I was tasked to do a P2V migration from let's say A to B...everything looked fine post migration. I took all the possible care to migrate the user permissions off of A to B, and as of now people seemed to have started complaining about access issues. I did compared apples with apples, but I did not see even a slight difference in the permissions on A, and B. However, it was strange to understand that a domain admin group that held sysadmin permissions on A, who also have the same sysadmin role on B, can access A, but they are facing issues with B...and I am like totally clueless on what else could go wrong.
I've even went to the extent of running, and finding out what members are part of that domain group, but all of them are part of it, and by running the query below. I can't see any issues from the SQL side.
exec master..xp_loginfo
@acctname = 'domain group'
@option = 'members'
I've also attached the error screenshots...any help or direction on this would be highly appreciated.
if you've completed a physical to virtual migration then permissions will already be in tact.
More information please?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 29, 2016 at 8:12 am
Hi Perry,
I used db mirroring in order to move out from physical to virtual. It was not a clone of the physical server, in case if that would've been the case then I wouldn't have had trouble with the permissions. I already had copied over the logins well ahead of the switch, and post the switch I even ran scripts to fix the orphaned users, and map proper permissions on the database front...what more information do you need?
Thanks
June 30, 2016 at 6:52 am
ffarouqi (6/29/2016)
Hi Perry,I used db mirroring in order to move out from physical to virtual. It was not a clone of the physical server, in case if that would've been the case then I wouldn't have had trouble with the permissions. I already had copied over the logins well ahead of the switch, and post the switch I even ran scripts to fix the orphaned users, and map proper permissions on the database front...what more information do you need?
Thanks
P2V in the virtualisation world means a clone of a machine, terminology conflict!
If you have mirrored a database to a new server you will need to migrate any logins, you can script them out, there are many scripts available to do this if you search around a little
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 30, 2016 at 11:42 am
Thanks! for letting me know what P2V means, but as I explained earlier I already had scripted out the logins, mapped the users, granted permissions etc. However, the issue is something which I am trying to figure out that even though the domain admin group is a sysadmin on new SQL instance, then why they still say they can't access the resources...I don't understand what could be wrong here.
Regards,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply