Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2008-09-02 : 13:29:10
|
| SQL 2000 The application wants to turn the Prod Database into a reporting database. They want 2 users to have the access they need (Using Cognos ReportNet Server) to 1. Read Production data.2. Create tables to support reports.3. Create stored procedures for the reports.My thought was to create a role for these users. And allow them create tables owned by that role, but prohibit them from creating (or dropping) dbo tables or updating dbodata. (Cognos will be using something called "Tablular SQL" which cannotuse # and ## type temporary tables)I am not finding a way to manage schema(owner) level permissions in SQL 2000. |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-09-02 : 15:55:31
|
| I think I got it. I was confused by my seriously flawed testing.It looks like it goes like this (pretty easy):Add user to the newly created role.User has no access to the dbo user tables unless granted.1. Grant select to the dbo tables needed for reporting.2. grant create procedure to theRole; We're done. The user can create any tables he wants withhimself or the Role as owner.Sound right? |
 |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-09-02 : 15:57:36
|
| Correction: #1 should be1. Grant select to the dbo tables needed for reporting TO THE ROLE. |
|
|
|
|
|
|
Grant Limited Table Create Permissions to an id