Solved

SQL Server authentication accounts per instance or per database

Posted on 2013-05-21
6
560 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

821 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