Solved

SQL 2008

Posted on 2013-12-27
4
245 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_
  • 2
4 Comments
 
LVL 8

Assisted Solution

by:vr6r
vr6r earned 333 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 333 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 167 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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