SQL 7 - DbOwner Status in Groups

We have a SQL 7 set of databases, and we need to grant dbowner permissions on specific databases to groups of people.  I have set up groups on NT, and in SQL, but the dbowner property fails when we run reports requiring sql to drop a table.  The dbowner property works, and the report runs fine, for users who are defined separately in sql (with the same dbowner properties as the groups).  PLEASE tell me that there is something I am missing...and that I don't have to enter each of my users as a separate SQL login.  Any suggestions/solution ideas are appreciated!
Who is Participating?
Mujeeb082598Connect With a Mentor Commented:
Hi :)

First tell me are objects created by all the users in the group and if it is true then object created by one user will not be droped by another user.

That is the reason when each user separetly connect and drop, the user is the only one who own the objects and that is why can drop without any problem.

To work as a group u should recommend the user to explicitly add dbo as the owner of the object during creation. This way all the users can access and drop the objects without any problem.

Hope this give u insight.
jhoranAuthor Commented:
How do you assign DBO rights to temp tables, when the stored procedure drops any leftover temp tables (the IF EXISTS command) at the beginning, then creates a temp table (SELECT * from TABLE A INTO TEMPTABLE B) for the SP, and drops all temp tables at the end of the SP?

These stored procedures are used to create online reports for several users which have been placed in a group in NT.
Hi :)

The way u are creating temp tables is not correct, temp tables in sqlserver are prefixed with the # sign (## sign for global temp tables), and are droped automatically when they are out of scope let say in your case they will exist for the duration you run the procedure and the moment u exit from the procedure the tables are dropped automatically (here i am talking about local temp table).

What u have to do is in your procedure first creat the table with the create table syntax and make sure you prefix it with the # sign and then insert the records into it using the INSERT...SELECT command.

For for information on using temp tables please see CREATE TABLE command syntax in books on line and read the Temporary Tables section.
jhoranAuthor Commented:
This worked!  Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.