February 14, 2007 at 7:28 am
How to find what are the existing logins in the SQL Server instance and also how to find what fixed server and database roles are to the logins in an sql server instance? I have 35 SQL servers in my environment and need to prepare spreadsheet for login and their fixed server and database roles information. Any help is greatly apprecciated.
Thanks,
BK
February 14, 2007 at 8:30 am
BK
Use the sp_helplogins stored procedure to get a list of how logins map to users and roles in databases. The query below will return a list of logins and what server roles they are in.
select
p.[name] as LoginName, p.type_desc as LoginType, r.[name] as RoleName
from sys.server_principals p
left join sys.server_role_members m
on p.principal_id = m.member_principal_id
and p.type in ('U', 'G')
join sys.server_principals r
on m.role_principal_id = r.principal_id
John
February 14, 2007 at 11:07 am
Here is some SQL that you can run:
-- For logins
Set nocount on
Select cast( serverproperty ('machinename') as nvarchar(128) ) as machinename
, coalesce ( cast( serverproperty ('instancename') as nvarchar(128) ) , 'default') as instancename
, loginname
, createdate
, updatedate
, dbname
, language
, denylogin
, hasaccess
, isntname
, isntgroup
, isntuser
, password
From master.dbo.syslogins
go
-- Server Roles
Select cast( serverproperty ('machinename') as nvarchar(128) ) as machinename
, coalesce ( cast( serverproperty ('instancename') as nvarchar(128) ) , 'default') as instancename
, spt_values.name as RoleName
, sysxlogins.name as LoginName
from master.dbo.spt_values spt_values
join master.dbo.sysxlogins sysxlogins
on spt_values.number & sysxlogins.xstatus = spt_values.number
where spt_values.low = 0
and spt_values.type = 'SRV'
and sysxlogins.srvid IS NULL
go
-- Database Users:
Set nocount on
Create table #dbusers
(dbname sysname
,username sysname
,loginname sysname
)
Exec master.dbo.sp_MSforeachdb @command1 = 'insert into #dbusers (dbname, username, loginname ) select ''?'' as dbname,sysusers.name as username,syslogins.name as loginname from [?].dbo.sysusers as sysusers join master.dbo.syslogins as syslogins on syslogins.sid = sysusers.sid where loginname ''sa'''
Select cast( serverproperty ('machinename') as nvarchar(128) ) as machinename
, coalesce ( cast( serverproperty ('instancename') as nvarchar(128) ) , 'default') as instancename
, dbname
, username
, loginname
From #dbusers
go
-- Database user roles
Set nocount on
Create table #dbuserroles
(dbname sysname
,username sysname
,loginname sysname
,rolename sysname
)
Exec master.dbo.sp_MSforeachdb
@command1
= 'insert into #dbuserroles (dbname, username, loginname,rolename )
Select ''?'' as dbname
, sysusers.name as username
, syslogins.name as loginname
, sysgroups.name as groupname
From master.dbo.syslogins as syslogins
Join [?].dbo.sysusers as sysusers
on sysusers.sid = syslogins.sid
Join [?].dbo.sysmembers as usergroups
on usergroups.memberuid = sysusers.uid
Join [?].dbo.sysusers as sysgroups
on sysgroups.uid = usergroups.groupuid
Where sysgroups.issqlrole = 1
And syslogins.name ''sa''
And sysgroups.name ''public''
'
Select cast( serverproperty ('machinename') as nvarchar(128) ) as machinename
, coalesce ( cast( serverproperty ('instancename') as nvarchar(128) ) , 'default') as instancename
, dbname
, username
, loginname
, rolename
From #dbuserroles
go
SQL = Scarcely Qualifies as a Language
February 15, 2007 at 8:02 am
If you need to run it across 35 servers and considering 3rd party tools- you can look at SQL Farms. You can run your sql script to get login info on all 35 servers in parallel and have one posture of all logins across all servers.
Some alternatives would be using serial linked server calls or osql.
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply