Wednesday, February 13, 2013

SQL Server:Best practice to set Database Owner name.


/*************How to find the owner of all the databases to match with database creation standards i.e. it should be in non expiry sql logins.
If we give any employee windows login, then the validity of the login will come to an end once the employee leave his job. In order to avoid those issues in future, we need to set owner of the database to 'SQL Login'*****************/

--'00000007' IS THE SQL AUTHENTICATED LOGIN for database owner. You can replace your login with ‘00000007'.


SELECT name AS DATABASE_NAME,owner_sid FROM SYS.databases
WHERE name IN(
SELECT SD.NAME AS DATABASE_NAME
--SL.loginname AS OWNERNAME
FROM MASTER..SYSLOGINS SL JOIN
SYS.databases SD
ON SL.SID=SD.OWNER_SID WHERE SL.loginname='00000007')

/***********How to find the owner of all the databases which doesn't match with database creation standards
i.e it should be in non expiry sql logins.*************/

SELECT name AS DATABASE_NAME,owner_sid FROM SYS.databases
WHERE name NOT IN(
SELECT SD.NAME AS DATABASE_NAME
--SL.loginname AS OWNERNAME
FROM MASTER..SYSLOGINS SL JOIN
SYS.databases SD
ON SL.SID=SD.OWNER_SID WHERE SL.loginname='00000007')

'00000007' IS THE SQL AUTHENTICATED LOGIN for database owner. You can replace your sql login with ‘00000007'.


/*************To find the non-standard database owner name by passing the above script output to below sid parameter value.
OWNER_SID SHOULD BE LIKE-'0x0105066666600051500000100'*************/

select name as owner_name from MASTER..syslogins where sid='input the parameter value'

/********How to change the owner of the database into database creation standards owner name? **************/

USE DATABASE_NAME
GO
select name as owner_name from MASTER..syslogins where sid=0x01
GO
--SP_CHANGEDBOWNER 'NEW_OWNER_NAME'
SP_CHANGEDBOWNER '00000007'

Hope you can maintain the owner name for all database as one sql login to follow the database creation standards by the above method.


No comments:

Post a Comment

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

  Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables Introduction: In the dynamic landscape of database management...