Getting error when replicating tables and data
fisogroup
Posts: 5
I am attempting to replicate table schema and data from server a to server b. The executable errors in the execution of this sql:
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'THEGOAT\IUSR_THEGOAT')
exec sp_grantlogin N'THEGOAT\IUSR_THEGOAT'
GO
sp_grantdbaccess N'THEGOAT\IUSR_THEGOAT', N'THEGOAT\IUSR_THEGOAT'
I don't understand why this is in the script to begin with, however server b will never have this user. I have selected only tables and data with the preferencses set to ignore permissions and users.
How do I get around this issue?
Thanks
Mike Johnson
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'THEGOAT\IUSR_THEGOAT')
exec sp_grantlogin N'THEGOAT\IUSR_THEGOAT'
GO
sp_grantdbaccess N'THEGOAT\IUSR_THEGOAT', N'THEGOAT\IUSR_THEGOAT'
I don't understand why this is in the script to begin with, however server b will never have this user. I have selected only tables and data with the preferencses set to ignore permissions and users.
How do I get around this issue?
Thanks
Mike Johnson
Comments
In the list of objects to script in the schema part of SQL Packager, make sure to de-select the users and roles that appear there, too. Just ignoring the permissions alone will not rid you of the users.
SQL Packager is trying to create a Windows login from one local machine account to another machine and that's not allowed.
I have deselected the users as well. However if I choose to transfer db objects such as proc or functions, it seems to want to create a user regardless of whether or not I have selected or deselected. Does the package interegate the permissions of objects and automatically attempt to create the users that have specific rights to those objects in the source database?
Now if I do tables only I can get the package to work. I would however like to transfer procs, views and fx's.
thanks
Mike
Yes -- if SQL Packager needs to set a permission, then it creates the necessary user if iut doesn't already exist. You can try using the 'ignore permissions' schema option to prevent this.
I have unchecked all objects, then I checked two tables which I would like to create on the target server.
I have also check the setting "Ignore user permissions & roles", but the script for creating users is still generated... Specifically, the error occurs at sp_grantdbaccess().
If I have unchecked the user as an object to script, shouldn't the script not include an attempt to create the user?!!!