June 25, 2014 at 12:50 pm
Hi All,
We are in the process of rolling out our new security strategy, and one of the developers testing the access came to me with an issue. Scripting a User-Defined Table Type using SSMS that has a default constraint give the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Property Text is not available for DefaultConstraint '[DF__TT_delive__is_de__6754599E]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Text&LinkId=20476
Table types without a default constraint script without issue, as do other object definitions.
His login/user has the following permissions:
VIEW ANY DEFINITION
db_datareader
db_datawriter
db_ddladmin
It was my impression that VIEW ANY DEFINITION would cover the scripting of any objects the devs might need, but clearly I was mistaken. I am able to script out this object when a member of the db_owner role, but I do not want to grant all developers this access.
Is there a permission I am missing that will allow our developers this ability without granting db_owner access?
July 9, 2014 at 4:31 pm
My first thought was maybe this was SSMS bug and nobody could do this. But just verified that I (a sysadmin) can script out definition of such a table type.
I don't know answer, but expect you could find it pretty quickly by running profiler and filtering so only see this one person's commands, then have them repro the problem. Include RPC start/complete, SQLBatch start/complete, most/all error/warning events and oledb errors. I expect you would see at some point a command followed by an error msg, which hopefully guides you in right direction.
July 9, 2014 at 5:35 pm
I haven't tested this myself, but I wonder if you have seen this post about the same issue?
http://serverfault.com/questions/258088/minimum-access-rights-for-generating-schema-creation-scripts
Unfortunately the Generate Scripts Wizard looks at sys.sql_expression_dependencies to find out what columns reference other columns. It does so even if you disable the exporting of foreign key constraints. So you have to give the user that wants to use the wizard permissions to SELECT from sys.sql_expression_dependencies as well as VIEW (ANY) DEFINITION.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 10, 2014 at 2:20 pm
mister.magoo (7/9/2014)
I haven't tested this myself, but I wonder if you have seen this post about the same issue?http://serverfault.com/questions/258088/minimum-access-rights-for-generating-schema-creation-scripts
Unfortunately the Generate Scripts Wizard looks at sys.sql_expression_dependencies to find out what columns reference other columns. It does so even if you disable the exporting of foreign key constraints. So you have to give the user that wants to use the wizard permissions to SELECT from sys.sql_expression_dependencies as well as VIEW (ANY) DEFINITION.
Thanks for the reply, mister.magoo. I granted SELECT access on sys.sql_expression_dependencies and still received the same error.
Mike Good (7/9/2014)
My first thought was maybe this was SSMS bug and nobody could do this. But just verified that I (a sysadmin) can script out definition of such a table type.I don't know answer, but expect you could find it pretty quickly by running profiler and filtering so only see this one person's commands, then have them repro the problem. Include RPC start/complete, SQLBatch start/complete, most/all error/warning events and oledb errors. I expect you would see at some point a command followed by an error msg, which hopefully guides you in right direction.
Thanks for the reply, Mike. I added the events you mentioned to a profiler trace and reproduced the issue. I don't see any errors or messages that indicate where this is failing. In fact, I don't see the error that the UI gives me. The final query in the trace is related to the constraint and TVT I am trying to script, though:
exec sp_executesql N'SELECT
cstr.definition AS [Text]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.default_constraints AS cstr ON cstr.object_id=clmns.default_object_id
WHERE
(cstr.name=@_msparam_0)and((clmns.name=@_msparam_1)and((tt.name=@_msparam_2 and SCHEMA_NAME(tt.schema_id)=@_msparam_3)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'DF__TT_delive__is_de__6754599E',@_msparam_1=N'is_deleted',@_msparam_2=N'delivery_method_fee_schedule_TVP',@_msparam_3=N'test_schema'
I can run this query without issue on the account that cannot script the object. Once I grant that account db_owner or sysadmin, I have no trouble scripting the table type.
Edit:
I tried scripting this same TVT on SQL Server 2008R2 and SQL 2014 with VIEW ANY DEFINITION, and it works perfectly fine. We are only having the issue against a 2012 instance.
July 10, 2014 at 3:52 pm
Confirmed. I was wrong to assume error would get thrown, sorry about that.
I think problem specifically comes from attempt to read definition column from sys.default_constraints. No error is thrown, we just see NULL for non-privileged user, and proper definition for privileged user (at least for sysadmin). http://msdn.microsoft.com/en-us/library/ms187113.aspx addresses this to some degree, lists this catalog view and this column in particular, but I'm not sure yet what solution is. Tried a few GRANTS but nothing worked. http://technet.microsoft.com/en-us/library/ms191507%28v=sql.105%29.aspx also lists these catalog views and their definition columns as exceptions, but offers no solution.
Simple script to repro this problem is select "top 1 definition from sys.default_constraints"
July 11, 2014 at 6:44 am
Mike Good (7/10/2014)
Confirmed. I was wrong to assume error would get thrown, sorry about that.I think problem specifically comes from attempt to read definition column from sys.default_constraints. No error is thrown, we just see NULL for non-privileged user, and proper definition for privileged user (at least for sysadmin). http://msdn.microsoft.com/en-us/library/ms187113.aspx addresses this to some degree, lists this catalog view and this column in particular, but I'm not sure yet what solution is. Tried a few GRANTS but nothing worked. http://technet.microsoft.com/en-us/library/ms191507%28v=sql.105%29.aspx also lists these catalog views and their definition columns as exceptions, but offers no solution.
Simple script to repro this problem is select "top 1 definition from sys.default_constraints"
That is interesting! Running that query on a privileged sysadmin user returns ((0))
Running it with the unprivileged user does indeed return NULL.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply