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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy