Discovering Orphaned Users

Adrian Buckman troubleshoots an access scenario:

First lets check that the User Does actually exist, we know the Server login exists otherwise the user would be complaining that they cannot connect to the SQL server instance.

Sure enough – there is the user ‘SQLUndercoverUser’ lets check out the permissions:

No problems there – the user has [db_datareader], [db_datawriter] and [db_owner] so we know there is not a permissions issue, so lets test this login by connecting to SQL server with the user credentials:

Connected to the Server with no issues, lets open a new query against SQLUnderCoverDB:

Hmm so despite having  permissions to access the database  we are receiving this error – we know that the password is correct too otherwise we wouldn’t be able to access the Server at all….

Adrian does a nice job of walking through the troubleshooting process, going from simple problems (does the user actually exist? does the user have permissions?) and into the real cause, which was orphaned SQL authenticated users.  Read the whole thing.

Related Posts

Securing S3 Credentials In Spark Jobs

Jason Pohl shows how to protect credentials for connecting to Amazon Web Services S3 buckets when building Spark jobs: Since Apache Spark separates compute from storage, every Spark Job requires a set of credentials to connect to disparate data sources. Storing those credentials in the clear can be a security risk if not stringently administered. […]

Read More

Using dbatools To Back Up SQL Logins

Claudio Silva has a post showing how to use the Export-SqlLogin cmdlet to back up SQL Server logins on all databases on a set of instances: With a database restore, the users are within a database and if their SID matches the SQL Login you are ready to go. But with the logins it is a […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

June 2017
MTWTFSS
« May  
 1234
567891011
12131415161718
19202122232425
2627282930