CODEWITHSUNDEEP
sql-server
user3688418
user3688418

Reputation:

How to understand that a user gets database access in SQL Server

I have been tasked with auditing security on my SQL Server. But, when I look at a login in SQL Server Management Studio, I don't see a checkbox beside the master db for that login. How can I determine what databases a login has access to?

Upvotes: 1

Views: 272

Answers (2)

Jason Clark
Jason Clark

Reputation: 1425

If you see a login match up to a user in this manner, then the login has access to the database.

SELECT sp.name AS 'Login', dp.name AS 'User' 
FROM sys.database_principals dp 
  JOIN sys.server_principals sp 
    ON dp.sid = sp.sid 
ORDER BY sp.name, dp.name;

you can do it in SQL Server 2005/2008

Upvotes: 0

A_Sk
A_Sk

Reputation: 4630

ther is a useful system store procedure to list all mapping user of SQL login account.

Syntax:

sp_msloginmappings @Loginname , @Flags

@loginname: the login account name, If loginname is not specified, results are returned for the login account(current login name)

@Flags: value can be 0 and 1, by default 0. 0 means show mapping user in all databases. 1 indicates how mapping user in current database context.

e.g here is sample:

sp_msloginmappings 'sa'
show mapping user account info in all databases for login account 'sa'

sp_msloginmappings 'sa', 1
show mapping user account info in current databases context for login account 'sa'

Upvotes: 2

Related Questions