Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spicy_sudhakar
Starting Member
6 Posts |
 Posted - 2004-11-24 : 07:50:11
|
| Hi Members,We have sp_Databases for get all databases names.Same as how we get all roles and information in all databases ??else any stored procedure avail ???My need is Role based security in my application.I create Role by using all database tables.and also i want to get all information of these roles.Waiting for ur reply.With RegardsSudhakar |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-24 : 13:28:25
|
| I don't understand what you are asking for.Tara |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-25 : 03:03:32
|
| not sure i understand your question but the query below gets the roles for the member name specified.declare @temp table(memberid int,membername varchar(100),groupuid int) declare @sql varchar(4000),@MemberName varchar(200) insert into @temp select usr.uid as MemberID,usr.name as MemberName, mem.groupuid as GroupID from sysmembers as mem join sysusers as usr on mem.memberuid=usr.uid where usr.name =@membername select t.groupuid,u.name,t.memberid,t.membername from @temp as t join sysusers as u on t.groupuid=u.uidorselect * from sysusers where coalesce(sid,'')=''run this for every database--------------------keeping it simple... |
|
|
|
spicy_sudhakar
Starting Member
6 Posts |
Posted - 2004-11-25 : 04:22:36
|
Hi Tara,Thnakz for ur reply.How we get all role names with permissions in all databases.is any stored procedue avail ???BySudhakarquote:
Originally posted by tduggan
I don't understand what you are asking for.Tara
|
|
|
|
spicy_sudhakar
Starting Member
6 Posts |
Posted - 2004-11-25 : 05:13:42
|
Hi jen,Thankz for reply.How we get all role names with permissions in all databases.is any stored procedue avail ???BySudhakar.quote:
Originally posted by jen
not sure i understand your question but the query below gets the roles for the member name specified.declare @temp table(memberid int,membername varchar(100),groupuid int) declare @sql varchar(4000),@MemberName varchar(200) insert into @temp select usr.uid as MemberID,usr.name as MemberName, mem.groupuid as GroupID from sysmembers as mem join sysusers as usr on mem.memberuid=usr.uid where usr.name =@membername select t.groupuid,u.name,t.memberid,t.membername from @temp as t join sysusers as u on t.groupuid=u.uidorselect * from sysusers where coalesce(sid,'')=''run this for every database--------------------keeping it simple...
|
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-26 : 02:15:28
|
| exec sp_helprotect 'objectname','username'run this for every user, object,database. i suggest using an sproc and table variables for this.--------------------keeping it simple... |
|
|
|
spicy_sudhakar
Starting Member
6 Posts |
Posted - 2004-11-26 : 02:35:39
|
| Thankz Jen.I already used same as this.what is sproc and table.Sudhakar |
|
|
|
|
|
|
|
|
How to get All roles in all databases ?