1

I have a DB running on SQL Server 2005 that has a user-defined data type. The user-defined data type is also implemented in the model database, so that when I create a temp table I can use the same user-defined data type.

For instance, in AppDB I have this datatype defined:

CREATE TYPE [dbo].[ product_code] FROM [varchar](8) NULL

And the exact same in Model:

CREATE TYPE [dbo].[ product_code] FROM [varchar](8) NULL

I’ve found that if the security login on my database instance has the sysadmin Server Role, then the user has no trouble calling stored procedures that create tables in tempdb using the product_code datatype—but if I remove sysadmin from the application security login, then the stored procedure call fails.

The same stored procedure call succeeds if I replace the user-defined datatype with the varchar system datatype throughout.

For security reasons I don’t want to add the sysadmin Server Role to the application security login—what options do I have if I want to continue using user-defined datatypes? Do these options change with SQL Server 2008?

Specifically, what permissions beyond those granted to public role are required? Answers that require edits to all stored procedures will be considered non-responsive.

Geoffrey McGrath
  • 1,663
  • 1
  • 14
  • 35
  • What permissions does the user have in AppDB? e.g. can they create a *local* table with that data type? – Aaron Bertrand Mar 04 '12 at 19:52
  • 1
    Also as an aside you may find this blog post useful. Documentation around your types is going to be much less hassle in the long run than trying to enforce it using alias types. https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-alias-types.aspx – Aaron Bertrand Mar 04 '12 at 20:02
  • Thank you for the article link--i've forwarded this on to our system architects for their review. I've just added the user to the db_ddladmin fixed user role and the problem is resolved--is this the right thing to do? I presume that is better than adding the user to the sysadmin role--but I admit I don't know much about this stuff. – Geoffrey McGrath Mar 04 '12 at 21:06
  • Heh, perhaps db_ddladmin is overkill--I see that creating the stored procedures with EXECUTE AS OWNER might make more sense, per this article: http://stackoverflow.com/questions/429051/should-control-permission-be-given-on-a-stored-procedure-in-sql-server-2005 – Geoffrey McGrath Mar 04 '12 at 21:23
  • I have a procedure which allows you to alter user defined data types. It should be a standard feature of SQL Server though. – Filip De Vos Mar 04 '12 at 22:31

2 Answers2

2

The problem with user-defined data types ( a.k.a aliases) and the tempdb is first that the user-defined data types must be defined in the model database, which is as has been done correctly here. However, the permission to manipulate those objects must be granted--the reasonable way to do this is to grant the db_ddladmin role membership to the model. When the SQL Server service is restarted, the created tempdb will "inherit" the db_ddladmin role membership. If restarting SQL Server is undesirable, go ahead and grant the db_ddladmin role membership directly to the tempdb for the applicaton user.

To add the db_ddladmin role programmatically to the model for user named "MyAppUser":

USE [model]
GO
EXEC sp_addrolemember N'db_ddladmin', N'MyAppUser'
GO

To be very clear, the db_ddladmin fixed user role does not need to be added to the application db, only to the model.

Geoffrey McGrath
  • 1,663
  • 1
  • 14
  • 35
-1

There should be no need to create a uddt in tempdb. If you want to match your data types of your temp tables, use:

select fieldx, fieldy into #temptable from sourcetable

or use:

declare @fieldx product_code

select @fieldx into #temptable

That way the field sizes match and there are no permission issues.

If you wish to create permanent tables in tempdb... My advice is to create a specific database for these tables.

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60