SQL Server database roles and GDPR

Corporate Audits and other regulatory initiatives such as GDPR , it is increasingly important to apply solid database access security practices. Users should only be able to view approved data . Avoid applying wider roles to users , such as db_owner when all the user needs is some access to 2 tables and a stored procedure. One of the tools available to the DBA is the database role. The principle of the database role is basic , and any sql server database comes with a set of default database roles such as db_datareader and db_owner. Always using the default database... Read more →

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 →