September 17, 2007 at 10:48 am
Can someone tell me the proper sql to get the DB ROLES from the DB? I think sp_helprole will get me this but I need it not for the current DB but for another.
I need a way to be connected to DB 'A', then through script, connect to DB 'B' and see all of the DB roles associated with DB 'B'.
Thanks
September 17, 2007 at 11:37 am
SELECT
name FROM otherdb.sys.database_principals WHERE TYPE = 'R' OR TYPE = 'A'
Regards,
Andras
September 17, 2007 at 11:56 am
One simple way is to build SP simular to the code below in which you would pass the desired db name and call that proc when needed. There are other methods but this will work and is simple to accomplish. You might wish to refine the select list just be careful of the quote marks.
create procedure usp_dbroles (@db varchar(50))
as
declare @SQL nvarchar(1000)
set @SQL = 'select '''+@db+''' as DBName,
name as RoleName,
uid as RoleID,
isapprole as IsAppRole
from '+@db+'.dbo.sysusers
where (issqlrole = 1 or isapprole = 1)'
exec sp_executesql @SQL
go
usp_dbroles 'pubs'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply