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 get the AD groups of a login with sys.login_token

Question: I’d like to get the list of AD groups of a login currently logged into a SQL Server. I’ve read your post on xp_logininfo limitations – and one of the issues I’m experiencing is that xp_logininfo only returns details from AD global groups not universal groups. This is limiting my ability to identify non global groups using xp_logininfo. Is there an alternative method from within SQL Server? Answer: There are alternatives I use – both from within and outside of SQL Server. From without you can use Powershell cmdlets – which are powerful ways of querying the AD –... Read more →

How to script CREDENTIALS for SQL Server migration

Question: I’m having some problems in migrating CREDENTIAL objects for SQL Server migrations. The CREDENTIALS are used as part of the authentication method for PROXY accounts. When attempting to migrate via the scripted method , I think the password is corrupting? What is a simple and effective method to script the CREATE statements and then include as part of the deployment package ? Answer: It can be awkward to migrate the CREDENTIAL objects depending on the method you use. One simple method is to execute this code on the source SQL Server , it will create the CREATE statement. The... Read more →

SQL Server , Meltdown and Spectre FAQ

I'm getting various questions about the Spectre and Meltdown impact on SQL Server. I'll start listing the most popular questions and the response. There is a lot of activity in progress across all the communities about mitigating the impact of Meltdown and Spectre . Typically there will be patching by different vendors and this needs to be managed properly. Where is the Microsoft SQL Server guideance documentation for Meltdown and Spectre ? The main link I reference is Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities . The main advice is start planning for patching. There is... Read more →

How to list CLR objects inside a SQL Server database

Troubleshooting an error message An error occurred in the Microsoft .NET Framework while trying to load assembly id xxx and needed to identify CLRs related to a particular database. This query lists the CLR objects inside a SQL Server database. Use the information in this query to start diagnosing the error messages. use myDB go SELECT o.object_id AS [object_ID] , a.assembly_id AS [assemblyID] ,a.name AS [assemblyName] ,schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] ,o.type_desc AS [CLRType] ,o.create_date AS [DateCreated] ,o.modify_date AS [SateModified] ,a.permission_set_desc AS [CLRPermission] FROM sys.objects o INNER JOIN sys.module_assembly_usages ma ON o.object_id = ma.object_id INNER JOIN sys.assemblies a... Read more →