Solved

SQL Server authentication accounts per instance or per database

Posted on 2013-05-21
6
571 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

751 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