Solved

SQL Server authentication accounts per instance or per database

Posted on 2013-05-21
6
548 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 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