Schema, Owner for Objects in Ms Sql

Schema, Owner for objects in MS SQL

The use of schemas is exceptionally beneficial when you have security concerns.

If you have multiple applications that access the database, you might not want to give the Logistics department access to Human Resources records. So you put all of your Human Resources tables into an hr schema and only allow access to it for users in the hr role.

Six months down the road, Logistics now needs to know internal expense accounts so they can send all of these palettes of blue pens to the correct location people. You can then create a stored procedure that executes as a user that has permission to view the hr schema as well as the logistics schema. The Logistics users never need to know what's going on in HR and yet they still get their data.

You can also use schemas the way cfeduke has suggested and just use them to group things in the object browser. If you are doing this, just be careful because you might end up creating Person.Address and Company.Address when you really just need a single dbo.Address (I'm not knocking your example, cfeduke, just using it to illustrate that both address tables might be the same or they might be different and that YMMV).

Change ownership of all objects in a database

If you are looking to change owners of just tables you can use the undocumented sp_MSforeachtable like this:

sp_MSforeachtable @command1="sp_changeobjectowner '?', 'new_owner'"

If you really need all objects then you'll need to iterate. One way (but not the only way) would be a cursor like this one:

DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)
DECLARE @currentOwner varchar(50)
DECLARE @newOwner varchar(50)

SET @currentOwner = 'ASPNET'
SET @newOwner = 'dbo'

DECLARE alterOwnerCursor CURSOR FOR
SELECT [name] FROM dbo.sysobjects 
WHERE 
  xtype in ('FN','IF','P','TF','U','V', 'TT', 'TF') --Modify list to add and remove object types*
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
   EXEC sp_changeobjectowner @qualifiedObject, @newOwner
   FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor

Cursor above is an untested and modified version of this.

*Note: The cursor query refers to a list of xtypes. Please review this for the full list.

Does SQL Server store the name of the user who originally created a User Defined Function?

Partially, if object was created not by the database owner, otherwise you will see a dbo as the creator:

USE db;
SELECT so.name AS [ObjectName],
       su.name AS [UserName]
FROM sysobjects so
    JOIN sysusers su
        ON su.uid = so.uid
WHERE so.id = OBJECT_ID('FunctionName');

---

Update: Martin Smith comment is correct. Starting from SQL Server 2005:

By default, when developers create objects in a schema, the objects
are owned by the security principal that owns the schema, not the
developer. Object ownership can be transferred with ALTER
AUTHORIZATION Transact-SQL statement.

Therefore, if ownership specified explicitly, it can be retrieved from sys.objects:

ALTER AUTHORIZATION ON OBJECT::myFunction TO someUser

-- returns non-null value after transfer:
SELECT principal_id FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('myFunction')

Otherwise, the newly created object is owned by "schema owner". So there is no way to query who was really the initial owner/creator..

As the last resort a suggestion of @John Cappelletti. His link to similar topic has an answer:

If the object was recently created, you can check the Schema Changes
History report, within the SQL Server Management Studio, which
"provides a history of all committed DDL statement executions within
the Database recorded by the default trace":

Why is it important to have schema or owner for a table when we can directly use the table itself

schema definition is quite different in different database systems.

but generally schema is used to categorize & organize some interrelated database objects, such as tables, Stored procedures , etc.

here are some application/advantage of using schema:

• You can apply security permissions for separating and protecting database objects based on user access rights.
• A logical group of database objects can be managed within a database. Schemas play an important role in allowing the database objects to be organized into these logical groups.
• The schema also helps in situations where the database object name is the same. But these objects fall under different logical groups.
• The schema also helps in adding security.
• It helps in manipulating and accessing the objects which otherwise is a complex method.

---

Related Topics