creating a custom database role in TempDB

Is there a way to create my own database role that has the db_ddladmin, db_datareader and db_edatawriter schemas so that I can assign users to this role instead of assigning the above 3 schemas to each user?
I'm trying to  allow users the ability to create temporary tables and the 'execute as owner' in a stored procedure that creates temporary tables doesn't give the caller the permissions.
RonPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

k_murli_krishnaCommented:
I am afraid that you may not be able to combine database/server level system roles into a user defined role.

Refer to these, everything is clearly laid out:
Database-Level Roles
http://msdn.microsoft.com/en-us/library/ms189121.aspx
Understanding SQL Server fixed database roles
http://www.mssqltips.com/tip.asp?tip=1900
SQL Server Security: Fixed Database Roles
http://www.sqlservercentral.com/articles/Security/sqlserversecurityfixeddatabaseroles/1231/
SQL Server security: Controlling access via database roles
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1344436,00.html

Also Refer:

Global Temp Table permissions
http://www.eggheadcafe.com/software/aspnet/30238938/global-temp-table-permiss.aspx
deny permission to create temporary tables
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/1e0079ec-ac46-4028-b5bb-3eff3831325d
http://www.windows-tech.info/15/ccbb2ef4c3fb648a.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G GodwinDatabase AdministratorCommented:
Even if you could do this, TempDB gets recreated when SQL Server restarts, so you would lose it.
This should not be necessary.  Are you saying that the owner of the PROC does not have Create Table rights?  I have never seen a condition where users were unable to create temp tables.  It is usually pretty wide open.  I am thinking that you may have a permission setting (possibly a deny) that is preventing the user from creating temp tables.  
-G
0
G GodwinDatabase AdministratorCommented:
I believe your problem may be that the Temp Table is being created by a stored proc, and therefore it disappears when the SP is finished.  
Here's a quote from BOL (books on line):
Temporary tables are automatically dropped when they go out of scope, [...]:
    • A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
You may need to use global temp tables, persistant (regular) tables, or table valued functions.  I would recommend table valued functions if possible, or simply let the stored proc return the results into a temp table created outside the proc.  Let me know if you need more on this.
-G
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RonPresidentAuthor Commented:
The answer it can't be done.
0
G GodwinDatabase AdministratorCommented:
>> The answer it can't be done.

Right.  However, it should not need to be done.
>>I'm trying to  allow users the ability to create temporary tables and the 'execute as owner' in a stored procedure that creates temporary tables doesn't give the caller the permissions.
I would bet that the users have the permissions to create the tables.  If the temp tables are created in a stored procedure, they will not be accessible outside the procedure unless you use global temp tables or one of the other options mentioned above.
-G
0
G GodwinDatabase AdministratorCommented:
Thanks.
-G
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.