Search code examples
sql-server-2005user-defined-typesdatabase-permissionstempdb

SQL Server 2005 user-defined data type, tempdb and permissions

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.

Solution

  • 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.