How I Know If I am DBOWNER or Not in SQL Server? – Interview Question of the Week #222

Question: How I Know If I am DBOWNER or Not in SQL Server?

Answer: This question was asked to me during Comprehensive Database Performance Health Check. While I proposed a few changes to my customers to the database, the DBA on the other side was worried that if we are allowed to make the changes or not. His worry was more in front of the permissions issues in SQL Server rather an approval from his manager.

Finally, we ran the following script to check if we are the DBOwner or not and based on the answer, we were able to make the necessary changes. Let us see a simple script which enables us with the knowledge if we are a member of the group Database Owner or not.

IF IS_MEMBER ('db_owner') = 1
PRINT 'You are a database owner.'
ELSE IF IS_MEMBER ('db_owner') = 0
PRINT 'You are NOT a database owner.'
ELSE IF IS_MEMBER ('db_owner') IS NULL
PRINT 'Error';
GO 

If you want a resultset, you can replace PRINT with SELECT.

Additionally, please note that you pretty much check any role with the use of function IS_MEMBER. After a user has been authenticated and allowed to log in to an instance of SQL Server, a separate user account must exist in each database the user has to access.

Let me know if you have used this function in the past for your application or business. I would love to know what was your business scenario.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version