8
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. ...
- 17.9k
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 ...
- 69.5k
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 ...
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 ...
- 51
5
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 ...
- 11.2k
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 ...
- 23.9k
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 ...
- 18.3k
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 ...
- 7,984
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 ...
- 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 ...
- 158
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 ...
- 334
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 ...
- 70.5k
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 ...
- 423
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 ...
- 397
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 @...
- 16.6k
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".
...
- 70.1k
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 ...
- 47.6k
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 ...
- 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 ...
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 ...
- 70.5k
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 ...
- 4,685
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 ...
- 18.3k
2
votes
Accepted
Cannot login to SQL Server as a member of AD group
I noticed similar issue and I was locked out of SQL Server after install. So I started SQL Server 2014, in single user mode with -m option and logged into server SQL Management studio. I verified that ...
- 142
2
votes
Active Directory to authenticate user access to Postgres DB through ODBC driver. Is this possible?
Active Directory has 3 kinds of authentication: NTLM (NT LAN Manager), Kerberos (GSSAPI), and LDAP. Active Directory is essentially a fancy LDAP server with extra authentication mechansims
LDAP is ...
- 8,583
2
votes
Difference Between Sign In and Login (Users and Groups)
I think arguing about login vs sign-in is sematics.
Sql server allows you to log in via 2 modes.
SQL authentication: means using a user id and password which is not the same as your domain ...
- 3,243
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 ...
- 2,053
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 ...
- 35.5k
2
votes
Accepted
Unable to connect using Azure AD Service Principal on SQL Server
Service principal is not supported in SSMS as well as in other SQL tools. Only application interface is supported for this type of AAD services.Your C# program works and this is the right way to ...
- 36
Only top scored, non community-wiki answers of a minimum length are eligible