| Author |
Topic |
|
mn757
Starting Member
15 Posts |
 Posted - 2010-12-23 : 04:48:32
|
| I have a number of objects for a database where the uid in sysobjects is related to a user that has been deleted. I have an existing user in sysusers and I need to change the uid in the sysobjects table to reflect that user for all the objects. I can't update the 'table' and I know that I shouldn't anyway, but I can't find other way of changing UID? Can anyone help?many thanksMartin. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-23 : 05:47:43
|
| You absolutely must not touch the system tables, changing them is a good way to completely toast your database if you don't know what you're doing. btw, sysobjects isn't even a table any longer. It's a view included only for backward compatibility with SQL 2000.What you want is the ALTER AUTHORIZATION command, that changes the ownership of a securable (like a table, view, etc). Look up the details in Books online--Gail ShawSQL Server MVP |
 |
|
|
mn757
Starting Member
15 Posts |
Posted - 2010-12-23 : 06:34:01
|
| Thanks for your reply. I was aware that the sys tables shouldn't be touched like that but I shall try the alternative you suggested. Many thanksMartin. |
|
|
|
mn757
Starting Member
15 Posts |
Posted - 2010-12-23 : 07:30:23
|
| Hi Gail,I've just tried this on a few objects and whilst it runs without errors the UID in sysobjects is still incorrect and related to a user that doesnt exist anymore.Do you know why this may be ?many thanksMartin. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-23 : 10:04:58
|
| No. What exactly did you run? btw, you shouldn't be using sysobjects. Use sys.objects instead--Gail ShawSQL Server MVP |
|
|
|
mn757
Starting Member
15 Posts |
Posted - 2010-12-23 : 11:52:48
|
| Hi,The issue is with sysobjects which returns different results to sys.objects. If I run this;use MN_testALTER AUTHORIZATION ON tcalendar TO xxxxxxx;GOwhere tcalendar is an object and xxxxxxx is a valiud user the uid of the object in sysobjects still seems to be linked to a deleted user.many thanksMartin. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-23 : 14:26:31
|
| Use sys.objects. sysobjects is for backward compat, so it's not always 100% accurate for SQL 2005. Remember there was a big change for ownership vs schema between SQL 2000 and SQL 2005. In SQL 2000 they're the same (mostly), 2005 they are not.Look at the object in sys.objects, that's the accurate and correct representation. What's the principal _id (the owner) and what's the schema_id (the schema the object is part of)? If principal_id is not null, what user does that value map to (sys.database_principals)? What schema does the schema_id map to (sys.schemas)?--Gail ShawSQL Server MVP |
|
|
|
mn757
Starting Member
15 Posts |
Posted - 2010-12-24 : 06:05:22
|
| Hi, thanks for your reply. The principal_id is null and the schema_id is 5 and relates to the correct database in sys.schemas. The principal_id in sys.schemas is 6. I guess I just need to try setting the principal id to 6 for the record in sys.objects ?thanks for your help, really appreciatedMartin. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-24 : 06:49:17
|
| A principal_id of null means the object is owned by dbo, which is ideally what you want in 99% of cases. Ignore that and leave it unless you fully understand the implications of a table owned by a different principal.sys.schemas contains schemas, not databases. Now, which schema is this table supposed to be in, the one with in id of 5 or the one with an id of 6? Or is it that the schema is owned by the wrong database principal?Please, take 5 minutes and read up on the schema/user separation in SQL 2005--Gail ShawSQL Server MVP |
|
|
|
|
sysobjects changing uid