Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Unanswered: Query to show what Login's have access to a specific DB

    I need a query that will show me what Login's have access to a specific DB. Ultimately, I want to run an autofix (to sync SIDS) for Login's that have access to on specific DB by using the first query. Thanks in advance. -A

  2. #2
    Join Date
    Oct 2010
    Posts
    4
    I think I found what I was looking for---

    select [name] from master..syslogins where [dbname] = 'uics_mstr'


    Any comments?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure where you are going with this concept, but if the SID values don't match then there isn't a user for the login that will allow access to this database. The "gotcha" to this logic is that if a login is a member of any of the server level "super user" groups like sysadmin, then that user will always have access to every database.

    The master.dbo.syslogins.database column is the default database at login for this login. It is the database where SQL Server will start new connections (spids) using this login. If a login has a default database and there isn't a SID match with a user in that default database, then the login itself is unusable (unless the cliient machine is running a SNAC that allows database override and the client code supports that concept).

    What exactly are you envisioning? I'm not clear on what you think is happening or will happen, but I'm sure that once I unnderstand I can help you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Use the below query

    SELECT u.name, o.name
    FROM syspermissions p, sysobjects o, sysusers u
    WHERE p.id = o.id
    AND u.uid = p.grantee
    AND u.name IN ('UserOne', 'UserTwo', 'UserThree')
    AND o.xtype = 'U'
    AND p.actadd = 27

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Jassi.singh, how will that query help?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by ecka00 View Post
    to sync SIDS for Login's that have access to specific DB
    on your specific DB execute the output from below query
    Code:
    select 'exec sp_change_users_login ''update_one'','''+name+''','''+name+''''
    from sysusers u where exists (select 1 from master..syslogins l where l.name=u.name)
    order by name

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •