Skip to main content
Share Your Experience: Take the 2024 Developer Survey
7 votes
Accepted

Grant Admin to an Active Directory account in SQL Server

Some of what you are asking for is actually pretty simple. Permissions for your AD Group You create a login for your AD group CREATE LOGIN [domain\AD Group] FROM WINDOWS Then grant it the access ...
Kenneth Fisher's user avatar
7 votes
Accepted

CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without enabling TRUSTWORTHY

No, an Asymmetric Key won't work, most likely because strong-naming assemblies changed in .NET 4.5 or somewhere around there, to now be Enhanced Strong Naming, which is not supported by SQL Server's ...
Solomon Rutzky's user avatar
7 votes
Accepted

User can drop table from a schema, but cannot create one

As an owner of an object, you can DROP that object. Regardless of whether you have CREATE privileges for such an object. Schema owner implies CONTROL on the schema which implies ALTER on the schema. ...
Tibor Karaszi's user avatar
6 votes
Accepted

Login cannot view definition even when granted the permission

Is UAC enabled? UAC can truncate the user's list of group memberships
Robert L Davis's user avatar
6 votes

Cannot login to SQL Server as a member of AD group

This is an old thread, but it was the first hit on Google when I encountered the same issue. My two cents: In my case it turned out that the AD group was a Distribution group, instead of a Security ...
Aaron Gonzalez's user avatar
5 votes

Cannot login to SQL Server as a member of AD group

TLDR; Make sure the user re-authenticates on the local windows machine that the client is establishing the connection from. From my experience, It seems SQL doesn't go check group membership when you ...
axawire's user avatar
  • 51
5 votes

Grant Admin to an Active Directory account in SQL Server

You can grant admin access using this code: CREATE LOGIN [DOMAIN\ADGroupName] FROM WINDOWS; GRANT CONTROL SERVER TO [DOMAIN\ADGroupName]; That will allow any user who is a member of [DOMAIN\...
Hannah Vernon's user avatar
  • 70.1k
5 votes

Why use domain account for SQL Server service?

If you are interested on how to configure accounts for each service in SQL Server, you can find good articles on MS-Docs. Configure Windows Service Accounts and Permissions Each service in SQL Server ...
McNets's user avatar
  • 23.8k
5 votes

Active Directory not working through SQL server Management studio

Normally when you receive an error message with the Error: 18456, ... it will contain additional information somewhere. Detailed SSMS Login Error In the case of the SSMS Login, the error message ...
John K. N.'s user avatar
  • 17.9k
5 votes

The Target Principal Name Is Incorrect. Cannot Generate SSPI Context (SQL or AD Issue)?

The SQL Server SPNs are not set on the computer account per se, but on the account that SQL Server is running under. You will need to create two SPNs for the SQL Server service if the service account ...
Tony Hinkle's user avatar
  • 7,954
5 votes

SSMS cannot determine if remote computer is connected to domain

This is an issue with the picker for selecting accounts when it cannot connect to the SMB port (or if NetBIOS isn't enabled, but you state that it works if you disable the firewall). You can either ...
Tom V's user avatar
  • 15.7k
4 votes
Accepted

Add Active Directory User For Azure-SQL-DB

When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest" email address created for the Azure ...
Murray Foxcroft's user avatar
4 votes

See who has access to databases via AD group membership

just seeing which groups the user is in that also have a login on the instance would be nice You can accomplish this using following code: exec xp_logininfo 'dom\acc', 'all'; Or you can ...
sepupic's user avatar
  • 11k
4 votes

CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without enabling TRUSTWORTHY

This answer is based upon Solomons suggestion above. The credit is all his. I rewrote my script as he suggested and it now works!!! TY SOLOMON! I am merely posting it as an example for others who come ...
SpaceGhost440's user avatar
4 votes
Accepted

The Target Principal Name Is Incorrect. Cannot Generate SSPI Context (SQL or AD Issue)?

Use setspn -X to look for duplicate SPNs for the SQL Server in question. Remove any duplicate SPNs that don't line up the SQL Server Service account in question. Service Principal Names for SQL ...
Hannah Vernon's user avatar
  • 70.1k
3 votes

Cannot login to SQL Server as a member of AD group

Clearly something was "hosed" with the original AD group. I created a second group which is essentially the same definition as the first, and verified the new group was working correctly. I then ...
Shoeless's user avatar
  • 397
3 votes
Accepted

How to change db owner after the associated AD account is disabled

Run this for each database. -- Change the database owner ALTER AUTHORIZATION ON DATABASE::databasename TO sa; GO To generate the script to change them all: SELECT 'ALTER AUTHORIZATION ON ...
MillhouseD's user avatar
3 votes
Accepted

Why does xp_logininfo truncates AD user account names? How to overcome this?

the way I get this done is: SELECT @@SERVERNAME ----myserver DECLARE @NTLogin nvarchar(128) ; SET @NTLogin = 'mydomain\myuser' DECLARE @...
Marcello Miorelli's user avatar
3 votes
Accepted

set Windows authentication for postgres

Yes Service_account_name = DomaninServiceAccount = Domain user A service account is actually a domain user, just with some extra settings like "no interactive login" and "password does not expire". ...
gbn's user avatar
  • 69.9k
3 votes

NTLM vs Kerberos authentication - questions

how can this be that while "Write ServicePrincipalName" privilege is turned off for the SQL service account, domain users are connecting using Kerberos auth That privilege is not required for ...
David Browne - Microsoft's user avatar
3 votes
Accepted

"Active Directory - Password" login in SSMS for Azure AD - Password expired

When you reset a password in Azure AD, it automatically forces the user to change the PW on the next login. If you immediately attempt to login to Azure SQL it will give you an error that the PW is ...
nsb53's user avatar
  • 176
3 votes

SQL Server Linked Server to Azure SQL using Azure AD

Unfortunately, this is not possible today. Standard Active Directory (AD) authentication works, however Azure Active Directory Authentication (AAD) is not implemented in the on-premises version of SQL ...
Conor Cunningham MSFT's user avatar
3 votes

Intermittent SQL Delegated Authentication Errors

If your SQL Servers are clustered, and their service accounts are configured to allow them to manage their own Service Principal Names, you might be running into a known issue. In an environment with ...
Hannah Vernon's user avatar
  • 70.1k
3 votes

What elements of sql server are impacted due to removal of domain/AD?

I assume that the to-be-decomissioned domain isn't the domain that Sql Server host is joined to. In that case, you need to move sql server host to another domain (guide, guide2 ). But Microsoft does ...
MBuschi's user avatar
  • 4,535
2 votes

The following system error occurred: No mapping between account names and security IDs was done

from the SQL management studio, connect to Analysis services and go to Roles, under the roles, the user that has been removed from active directory will appear in question marks "????????????????" , ...
AmmarR's user avatar
  • 2,806
2 votes
Accepted

Unknown Windows SID Mapped to Proxy During SSIS Package Execution

Check the blog post I wrote concerning how to identify this S-1-9 SID. From that post: This can happen as a result of using EXECUTE AS USER = 'SomeUser'; to switch context to another user that ...
Hannah Vernon's user avatar
  • 70.1k
2 votes
Accepted

Should domain "Users" group be removed from the SQL logins for database security?

Don't remove any SQL Login's until you are sure they are no longer required. Now you might be mixing two definitions. SQL Server databases contain database users SQL Server instances contain SQL ...
John K. N.'s user avatar
  • 17.9k
2 votes
Accepted

See who has access to databases via AD group membership

I was able to combine suggestions here with an old script I had lying around to come up with the following, which will show all the groups a user is in, along with the database roles they are mapped ...
SomeGuy's user avatar
  • 2,003
2 votes

SQL Server: Mixed mode security: Can a non AD user (a local user, remote client) have a trusted connection to SQL?

I now have a situation of a web app that can no longer run on as a domain user (long story). The web server can't run under the account or the application context can't run under the account? For ...
Sean Gallardy's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible