November 30, 2007 at 8:19 am
I need to see what databases are associated to each local SQL login. I am not sure where this information is stored? sys.sql_logins only lists the defaul database
Thanks for your help
Sandra
November 30, 2007 at 9:03 am
Logins aren't mapped to databases. Logins are mapped to users, which are in each database.
You need to scan the users in each database and relate those back to the login.
November 30, 2007 at 9:36 am
Thank you for the clarification.
what I actually need is to write 1 query that will list what databases were selected under user Mapping when the login was created...
I know I can see them, but I am not sure who to quey for those
November 30, 2007 at 9:51 am
Don't have SQL 2K5 on hand or the BOL but in 200 you could do this
SELECT
SU.[Name],
SL.[Name]
FROM
dbo.sysusers SU
INNER JOIN
master.dbo.syslogins SL
ON
SU.Sid = SL.SID
might be sys.user and sys.logins but you will need to find the couterpart. Also, probably something easier builtin.
November 30, 2007 at 9:58 am
Thank you so much!
November 30, 2007 at 10:00 am
I believe logins can be mapped to the DB, but, it is the DB / security / users that has the user mapping / securables. At that point you may deploy or use the database or application roles...
I could be wrong, elaboration anyone??
[see : server/security/logins/ pick one user get the properties and check the user mappings]
Cheers,
John Esraelo
November 30, 2007 at 10:18 am
jahanz2003 (11/30/2007)
I believe logins can be mapped to the DB, but, it is the DB / security / users that has the user mapping / securables. At that point you may deploy or use the database or application roles...I could be wrong, elaboration anyone??
[see : server/security/logins/ pick one user get the properties and check the user mappings]
Not sure I understood you here, but this is what I thought you were asking.
Logins are mapped to the server, some server roles at the server level provide universally across the databases such as Server Administration. Or a user can be mapped for access to a Database, where the datbase can control object access (barring not overridden by a server role). The access can be controled either directly to the user or to a role in which the user is assigned. At the database level most restrictive permissions apply when evaluating GRANT or DENY, so if a person has permissions themselves with GRANT and they are in a role where the same object has DENY then DENY is the outcome. Now as for mapping of users the name within the database is a User Alias that is mapped back to a server login (SQL Auth or Windows Auth) and not neccessarily the actual user name. In effect I can create a SQL login account Antares686 and grant access to a database but instead there use an alias of James (my real name BTW) but most people just opt to have the alias the same as the login.
Hope that all made sense, answered your question and wasn't too cludged to read.
November 30, 2007 at 10:31 am
Here are some statments for SQL Server 2005.
IF OBJECT_ID('tempdb..#DatabaseUsers') is not null drop table #DatabaseUsers
create table #DatabaseUsers
(DatabaseNamesysname not null
,DatabaseUserNamesysname not null
,LoginNamesysname null
)
-- sys.database_principals.type is S = SQL user, U = Windows user, G = Windows group
exec master.dbo.sp_MSforeachdb
@replacechar = N'?'
,@command1 =
'insert into #DatabaseUsers
(DatabaseName,DatabaseUserName,LoginName)
select''?''
,DatabaseUsers.name as DatabaseUserName
,suser_sname( DatabaseUsers.SID) as LoginName
from[?].sys.database_principalsas DatabaseUsers
whereDatabaseUsers.type in (''S'',''G'',''U'')'
select * from #DatabaseUsers
SQL = Scarcely Qualifies as a Language
November 30, 2007 at 11:01 am
Awesome. Thank you so much
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply