?
Solved

SQL 2008

Posted on 2013-12-27
4
Medium Priority
?
248 Views
Last Modified: 2014-01-16
Hi,

I am trying to get a SQL script to list out all the users for a database.  Please let me know the best way to export the security user information.
0
Comment
Question by:Jack_son_
[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
  • 2
4 Comments
 
LVL 8

Assisted Solution

by:vr6r
vr6r earned 1332 total points
ID: 39742283
Try this - returns users for the db in your current connection:
SELECT * FROM sys.database_principals

Open in new window

0
 

Author Comment

by:Jack_son_
ID: 39742378
will this pull all of the Security users?
0
 
LVL 8

Assisted Solution

by:vr6r
vr6r earned 1332 total points
ID: 39742640
Yes, relevant to your current database connection.  For example, if you open a connection on your sql server to database "test_db" and run that command, it will give you all the defined users to "test_db".
0
 
LVL 9

Accepted Solution

by:
QuinnDex earned 668 total points
ID: 39742645
;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

Open in new window


Basically what it does it gets the permissions granted and denied, and unions it with the role membership. It should give you a brief look on the security for server logins.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 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