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 |
|
SQLboom
Yak Posting Veteran
63 Posts |
 Posted - 2004-03-02 : 00:12:20
|
| I have a user with "public" profile.The user is provided with execute rights on a procedure and relevant rights on all it's dependent objects. This procedure creates a Local Temporary table with an identity column and tries to reset it's identity with DBCC Checkident.When this procedure is executed, it gives and error as "User does not have rights for using DBCC CheckIdent". BOL says that DBCC CheckIdent permissions are for Table Owner apart from sysadmin. Since the Local Temporary table is CREATED inside the procedure, why this error is coming..Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-02 : 00:46:17
|
| Most DBCC commands are administrative commands and can only be run by a sysadmin, regardless of what kind of table you're trying to run it against. |
 |
|
|
SQLboom
Yak Posting Veteran
63 Posts |
Posted - 2004-03-02 : 01:17:03
|
| BOL says:PermissionsDBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role, and are not transferable.That's the problem. |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-03-02 : 04:06:21
|
| Why would you want to reset the identity column on a temp table? I doubt it that SQL server determines an owner for a local temp table. If I were you I would rethink and rewrite the procedure. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-02 : 12:59:34
|
| The table owner would probably be dbo though. Does the user have db_owner role?Also, you should not be adding permissions to the dependent objects when using stored procedures unless you are using dynamic sql. One of the reasons to use stored procedures is because you only have to provide EXEC permission on it and no permissions on the dependent objects. This isn't true when using dynamic sql, but that's also one of the reasons why not to use dynamic sql.Tara |
|
|
|
SQLboom
Yak Posting Veteran
63 Posts |
Posted - 2004-03-03 : 02:11:13
|
| db_owner rights were not set. I tried with "Grant Create Table" set for the user. It did not work. db_owner would be like granting all permissions to the database which won't be possible in out case. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-03 : 08:31:06
|
| Just use the truncate table command if you are going to keep using the temp table in the same procedure.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
permission for "public" user