Ownership is changed while synchronizing databases ...
masi
Posts: 10
As a new user of SQL Packager I noticed a strange behaviour in the product.
This can be caused by insufficient knowledge of the product and how to use the different options. It can also be explained by a minor flaw in the product.
My problem is like this;
I have a role ‘RiskControl_Admin’ defined in my MS SQL Server database. Members of this role are Windows authenticated user i.e. ‘SEK\MaSi’.
The role has been granted privileges to create procedures which means that if the user creates a stored procedure without a fully qualified name i.e. “CREATE PROCEDURE x AS RETURN 1†it will be stored in the sysobjects table with the uid-column pointing to the login who issued the TSQL-statement (‘SEK\MaSi’).
If I run SQL Packager to upgrade an existing database it will NOT behave as expected. The result will be that the SPROC x will get ‘dbo’ as owner in the target database which is not what was expected.
Any ideas ?
This can be caused by insufficient knowledge of the product and how to use the different options. It can also be explained by a minor flaw in the product.
My problem is like this;
I have a role ‘RiskControl_Admin’ defined in my MS SQL Server database. Members of this role are Windows authenticated user i.e. ‘SEK\MaSi’.
The role has been granted privileges to create procedures which means that if the user creates a stored procedure without a fully qualified name i.e. “CREATE PROCEDURE x AS RETURN 1†it will be stored in the sysobjects table with the uid-column pointing to the login who issued the TSQL-statement (‘SEK\MaSi’).
If I run SQL Packager to upgrade an existing database it will NOT behave as expected. The result will be that the SPROC x will get ‘dbo’ as owner in the target database which is not what was expected.
Any ideas ?
This discussion has been closed.
Comments
This does happen because the current version of Packager relies on the data definition for the object to determine ownership. If the object is scripted as CREATE PROC x, then x will be created with the logged in user as the owner.
The only thing that you can do to change this is to alter the stored procedure's definition to include the ownership, for example CREATE PROC MaSi.x AS... in the database before running Packager.
I believe we are going to include some user-mapping options in future versions of Packager. These look to be almost a year away, though.
Should be a really easy task to do since all the required information is to be found in sysobjects-table.
OK - meanwhile I have to follow your advice which might cause us some problems due to users who can't stick to the 'new rules' forcing them to enter fully qualified names to their objects.
Looking forward to the new release whenever it pops up.
Thanks !