March 20, 2008 at 11:21 am
could you plz tell me the SQL script to display the database names and logins for all databases on a particular server
March 20, 2008 at 11:54 am
SQL2K
use master
go
select name from sysdatabases
select name from syslogins
SQL2K5
use master
go
select name from sys.databases
select name from sys.server_principals where type in ('U', 'S')
On the 2K5 you will get only sql and windows logins, not groups or roles.
March 20, 2008 at 12:09 pm
for SQL Server 2005
create table #DBUSERS
(
DBNAME varchar(50),
USERNAME varchar(50),
MEMBERNAME varchar(50),
OBJECTNAME varchar(100),
PERMGRANTED varchar(200),
PERMDENIED varchar(50),
COLUMNPERM varchar(10)
)
declare @cmd nvarchar(4000)
declare @cmd1 nvarchar(4000)
set @cmd =
'insert into #DBUSERS (DBNAME,USERNAME,MEMBERNAME)
select s.name,
a.name,
b.name
from ?.sys.sysusers a, ?.sys.sysusers b, ?.sys.sysmembers c,master.sys.sysdatabases s
where a.uid = c.memberuid and c.groupuid = b.uid and s.name like ''?'''
set @cmd1 =
'insert into #DBUSERS (DBNAME,USERNAME,OBJECTNAME,PERMGRANTED,PERMDENIED,COLUMNPERM)
select s.name,a.name,c.name,b.actadd,b.actmod,
case when
(
b.seladd is not null or
b.selmod is not null or
b.updadd is not null or
b.updmod is not null or
b.refadd is not null or
b.refmod is not null
)
then ''Y''
else ''N''
end
from ?.sys.sysusers a, ?.sys.syspermissions b, ?.sys.sysobjects c,master.sys.sysdatabases s
where a.uid = b.grantee
and b.[id] = c.[id] and b.grantee <> 0
and s.name like ''?'''
print @cmd1
exec sp_MSforeachdb @command1 = @cmd,@command2 = @cmd1
select distinct USERNAME,DBNAME,MEMBERNAME from #DBUSERS order by 2
drop table #DBUSERS
SQL DBA.
March 20, 2008 at 12:31 pm
http://www.sqlservercentral.com/articles/Security/61678/
This talk about security and will give you everything you need.
March 20, 2008 at 12:51 pm
I need like loginname instead of member name
I changed the membername to loginname? but the query result giving me NULL in col loginname.
could you plz help me
thanx
March 20, 2008 at 1:07 pm
You just want all the databases, and all the server logins?
SELECT * FROM sys.databases
SELECT * FROM sys.syslogins
March 21, 2008 at 7:33 am
hey garry this is rag...add me to the buddy list and msg me when needed i shall try if poss...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply