June 8, 2010 at 12:45 am
hi all,
can anyone who is very much perfect in queries tell me how to list out all the logins who have db_owner acess on a single database?
thanks.
June 8, 2010 at 2:04 am
check this topic
http://www.sqlservercentral.com/Forums/Topic411310-338-1.aspx
June 8, 2010 at 2:19 am
Try This
declare @sql2 varchar(8000),
@dbname char(30),
@sql3 varchar(8000),
@sql4 varchar(8000)
DECLARE curDBNames CURSOR FOR
SELECT DISTINCT name
FROM master..sysdatabases
WHERE name NOT IN ('master','model','msdb','distribution','tempdb')
/*
SELECT DISTINCT NAME
FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER','MODEL','MSDB','DISTRIBUTION','TEMPDB')
*/
open curDBNames
fetch curDBNames into @dbname
while @@fetch_status = 0
begin
select @sql2 =
'select ''' + RTRIM(@dbname) + ''' as DB_Name, substring(user_name(sm.role_principal_id),1,13) as Role,
substring(su.name,1,24) as ''Role Member''
from ['
+ RTRIM(@dbname) + '].sys.database_role_members sm JOIN ['
+ RTRIM(@dbname) + '].sys.database_principals su
on sm.member_principal_id = su.principal_id
--where issqlrole = 0
where user_name(sm.role_principal_id) like ''db_%'' and substring(user_name(sm.role_principal_id),1,13) = ''db_owner''
order by 1,2,3 '
print @sql2
Print '============================================='
SELECT
CONVERT (Char (18),o.name) as 'Database Name',
Convert (Char (25),l.loginname) as 'Database Owner'
FROM master..sysdatabases o
left join master.dbo.syslogins l
on l.sid = o.sid
--where dbid = db_id()
where o.name = @dbname
exec (@sql2)
fetch curDBNames into @dbname
end
close curDBNames
deallocate curDBNames
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
June 8, 2010 at 4:54 am
THANKS FOR THAT..
But i have one more doubt which is
see i have a login called FMUSER , it has acess to database GFRAG with username as fmuser and same login has acess to one more database called FRAG with username as dbo...
my queston is in above scenario what does dbo mean ..it should be fmuser right..why it is dbo...instead of fmuser or some other name
June 10, 2010 at 4:21 am
In the management studio, access the login properties of FMUSER under the security and logins folder go to the user mappings tab you can see the actual mappings for the user, looks like someone mapped the FMUSER to dbo user of the FRAG database.
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply