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 |
|
sonnysingh
Starting Member
42 Posts |
 Posted - 2005-05-12 : 09:46:09
|
| Hi FolksI am extracting name of users and roles from sysuser table into temp table and then create user member of those roles.. There ae two issues here1. need to find out the users which aren't member of any roles.2. need to find out the users which are member of any roles.so i can execute the sp_addrole dynamic command accordingly..Please any one can help....Start here......................................................DECLARE @USQL nvarchar(4000)DECLARE @sourcedb varchar(100)DECLARE @targetdb varchar(100)DECLARE @dbname nvarchar(4000)DECLARE @uname varchar(100)DECLARE @rname varchar(100)DECLARE @adrolemem varchar(100)CREATE TABLE #UsrRoleTABLE (DatabaseName sysname NOT NULL, UserName sysname NOT NULL, RoleName sysname NOT NULL)SELECT @USRSQL = 'INSERT INTO #UsrRoleTABLE SELECT N'''+ @targetdb +''', a.name, c.nameFROM ' + (@targetdb) + '.dbo.sysusers a INNER JOIN ' + (@targetdb) + '.dbo.sysmembers b ON a.uid = b.memberuid INNER JOIN ' + (@targetdb) + '.dbo.sysusers c ON b.groupuid = c.uidWHERE (c.name <> ''db_owner'')'EXECUTE sp_executesql @USRSQL/** Assign roles **/ DECLARE cur_addrole cursor forward_only FOR SELECT DatabaseName,UserName,RoleName from #UsrRoleTABLE OPEN cur_addrole FETCH NEXT FROM cur_addrole INTO @dbname, @uname, @rname WHILE @@fetch_status = 0 BEGIN SET @adrolemem = ' EXEC sp_addrolemember ' + @rname + ',' +@uname EXEC ('Use '+@dbname+@adrolemem) SET @adrolemem =' ' FETCH NEXT FROM cur_addrole INTO @dbname, @unameENDCLOSE cur_addroleDEALLOCATE cur_addroleGOEnd here..............................................Thanx in AdvanceSqlIndia |
|
|
|
|
|
Probs with T SQL for exracting Users and Roles