March 15, 2018 at 8:27 am
Everyone, I am wondering if I can get some help. I created a schema 'gb' schema and then changed the owner of that schema to 'abcuser' and created a table under that gb schema, so the idea behind this practice is to follow the consistency so the object we create in our DBs have just 1 owner but the owner of that table which I created is showing as someone else. Its not me or the schema owner even though I created a table. It is showing as someone else as the owner. And the reason why we are so concerned about it is that we have auditing in place and when developers ran the below query and shared the result with us, my manager name was showing as an owner of some tables which he didn't create. This is the query they ran.
select SCHEMA_NAME(o.schema_id) as SchemaName, so.name as TableName, so.uid, su.name as UserName
from sysobjects so
JOIN sysusers su on so.uid=so.uid join
sys.objects o on o.object_id = so.id
where SCHEMA_NAME(o.schema_id) = 'gb'
March 15, 2018 at 12:49 pm
NewBornDBA2017 - Thursday, March 15, 2018 8:27 AMEveryone, I am wondering if I can get some help. I created a schema 'gb' schema and then changed the owner of that schema to 'abcuser' and created a table under that gb schema, so the idea behind this practice is to follow the consistency so the object we create in our DBs have just 1 owner but the owner of that table which I created is showing as someone else. Its not me or the schema owner even though I created a table. It is showing as someone else as the owner. And the reason why we are so concerned about it is that we have auditing in place and when developers ran the below query and shared the result with us, my manager name was showing as an owner of some tables which he didn't create. This is the query they ran.
select SCHEMA_NAME(o.schema_id) as SchemaName, so.name as TableName, so.uid, su.name as UserName
from sysobjects so
JOIN sysusers su on so.uid=so.uid join
sys.objects o on o.object_id = so.id
where SCHEMA_NAME(o.schema_id) = 'gb'
It's going to be problematic no matter what if you are using that query. It's pretty useless. Look at the joins carefully. You can get what you need using sys.tables:SELECT
SCHEMA_NAME(T.schema_id) AS SchemaName,
T.name AS TableName,
USER_NAME(T.principal_id) AS OwnerName
FROM sys.tables AS T
WHERE T.schema_id = schema_id('gb')
When the owner (principal_id) is null it indicates the object is owned by the owner of the schema. That is the default behavior in terms of ownership.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply