How to search a different Active Directory Domain with Powershell Get-ADGroupMember

Question: I'm using the Powershell Get-ADGroupMember to iterate through various Groups and return the members. This is working OK - as long as I'm searching in AD groups which are on the same domain server as the server I'm currently logged on. For example - I've logged onto the domain : mydomain1.admin.net . I have authority to check AD groups in this domain. But if I then attempt to do a search on another domain e.g mydomain2.admin.net , even though the domain is in the same AD farm , the Get-ADGroupMember will not find the group. To make things more... Read more →

How to check a SQL Login is part of the SYSADMIN role

Question: As part of a regular SQL Server Violations reporting and checking any vulnerabilities on SQL Servers, I run regular scans and report on elevated server roles. What is an efficient method to identify if a login is using the sysadmin role? Answer: I use the IS_SRVROLEMEMBER function , which allows you to check on various server roles. As well as sysadmin , it is possible to also check on serveradmin,dbcreator,setupadmin,bulkadmin,securityadmin,diskadmin and processadmin. The IS_SRVROLEMEMBER returns either a value of 1 or 0. 1 = the login is a member of the role 0= the login is not a member... 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 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 →