How to find a SQL schema owner name

Question: How can I find the owner of a SQL Server schema ? I want o find the owner through a t-sql solultion , rather than looking through the GUI. Answer: To find a schema owner you can use either sys.schema view or the information_schema.schemata. Since SQL 2005, information_schema.schemata has come into line with sys.schema. information_schema.schemata returns schemas just from the current database. Find examples of the twp approaches below. If you're using the sys.schemas method, you'll need to use the schema_name() and user_name() functions to extract the names. use msdb go select schema_name(schema_id) as schemanames, user_name(s.principal_id) as usernames from... Read more →

select current_user shows dbo

Question: I'm logged onto a SQL Server 2016 instance using Windows Authentication and executing SELECT CURRENT_USER. Although I have sysadmin rights - the result always returns dbo. What I need is to return my domain account name. Can you help? Answer: There is a difference between CURRENT_USER and SYSTEM_USER. SELECT CURRENT_USER - Returns the name of the current user. This function is equivalent to SELECT USER_NAME(). If the current user is a sysadmin privilege , the recordset returns “dbo”, rather than the logon name. Which because your current logon has sysadmin privileges explains the dbo result SELECT SYSTEM_USER (also synonym... Read more →

How to create a Linked Server for SQL Server to PostgreSQL

Question: I need to create a SQL Server Linked Server connection from a SQL Server to a PostgreSQL database on Linux. The purpose is to check through a basic test from INSERT and SELECT statements for data insert and retrieval . I've checked through a few sites and there are different approaches - do you have a standardized method which you utilize for this purpose.? Answer: Increasingly I'm getting more requests within my own projects to create connectivity between different database server systems. It's not uncommon to link SQL Server with all other systems such as DB2 LUW, Oracle, MariaDB... Read more →

How to find computed columns on a SQL Server table

Question: I'm having a problem with an index rebuild job , which is failing on tables with computed columns. I've read ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF which details a solution for situations of computed columns, xml data type and LOB data types. How can I find the computed columns on sql tables? Answer: These are two ways in which you can identify the computed columns. One method is through the sys.columns and the other through sys.computed_columns . sys.computed_columns has the benefit of not requiring you to use the "is_computed = 1" predicate. Method 1 -... Read more →

Should I use SQL Change Data Capture for data audit ?

Question: There is lots of information around the SQL Server Change Data Capture (CDC) feature, and I’m trying to decide if CDC is suitable for the purposes of a project which requires data auditing. We currently have a sql server triggers based solution in place for capturing data changes on certain tables. Could you outline some the issues to consider when deciding if CDC is an appropriate tool. Answer: Deciding on when to adopt CDC is dependant on the specific requirements. The first step is to understand in detail the project requirements. As you mentioned in your question, there are... Read more →