Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server authentication accounts per instance or per database

Posted on 2013-05-21
6
Medium Priority
?
582 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 2000 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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