asked on

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!

ASKER CERTIFIED SOLUTION

Mujeeb082598

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jhoran

ASKER

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.

Mujeeb082598

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.

jhoran

ASKER

This worked! Thanks!