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.

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

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)

, ,
Previous Post
How to check the ANSI Compatibility of SQL Server Queries? – Interview Question of the Week #221
Next Post
Dirty Pages – How to List Dirty Pages From Memory in SQL Server? – Interview Question of the Week #223

Related Posts

Leave a Reply

Menu