Showing posts with label Owner. Show all posts
Showing posts with label Owner. Show all posts

Tuesday, September 30, 2014

What is dbo in SQL Server?

dbo stands for DataBase Owner. It is a special database user that maps to the owner of the
database. When created, a database is assigned an owner, which is the login that created it.
You can query the database owner using one of the following queries:


SELECT SUSER_SNAME(owner_sid)
 ,NAME
FROM sys.databases;

-- or :
SELECT SUSER_SNAME(sid)
FROM sys.database_principals
WHERE principal_id = USER_ID('dbo');

This login is automatically mapped to the dbo special user, and thus is granted all
permissions in the database. You can change the owner of a database by using the
following command:

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;


This will change the owner of the marketing database to the sa login.

Monday, September 22, 2014

Change the ownership of SQL Server agent jobs

The jobs may start failing when their AD accounts are removed. If the users have left it is better to change the job owner to a different account.
To ensure such issues wont happen, it is better to create a generic AD account with only necessary permissions and use it exclusively for this purpose.

List down all the jobs which are tied to the users and share it with all the respective project managers and technical architects and get a approval .
Decide on a common user account and create new one .