Solved

SQL Server authentication accounts per instance or per database

Posted on 2013-05-21
6
538 Views
Last Modified: 2013-06-06
Do you have accounts (SQL Authentication) per SQL Server instance or per SQL Server database? I was aware the password hashes are stored in sysxlogins table. But I wasnt sure if there was a sysxlogins table per database, or one per instance? If its per instance, how can you tell what permissions each account has over which database? Can you provide perhaps a query to list out the permissions by SQL authentication accounts.
0
Comment
Question by:pma111
  • 4
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 39184168
Kindly try using the permissions_audit.txt file present in the below forum which can help you out..

http://www.sqlservercentral.com/Forums/Topic745531-359-1.aspx
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39184169
There are some inbuilt SQL Server functions and procedures to view the list of permissions for a login. For detailed study either you can go to MSDN or the below link.

http://www.mssqltips.com/sqlservertip/1071/auditing-your-sql-server-database-and-server-permissions/
0
 
LVL 3

Author Comment

by:pma111
ID: 39184422
Thanks. But as per the question, is there a set of accounts per database, or per instance. Is there a sysxlogins per database or per instance?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39184706
Ideally syslogins table will be available in the master database per instance and will be shared across all user databases and sysusers table will be present in all user databases with individual entries inside it.
0
 
LVL 3

Author Comment

by:pma111
ID: 39186725
Do all the later versions of SQL Server still support the mixed mode authentication?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39186781
Yes, all SQL Server versions supports Mixed Mode Authentication..
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now