June 7, 2011 at 9:40 am
how to run the below qury in all databases
CREATE TABLE #TempPermissions
(
uid smallint,
UserName varchar (100),
Group_Name varchar (100),
NT_Group varchar (3),
NT_User varchar (3),
SQL_USer varchar(3)
)
INSERT #TempPermissions
SELECT su.uid, sl.name, su2.name 'Group Name',
CASE WHEN
su.isntgroup=1 THEN 'Yes' ELSE 'No' END 'NT Group',
CASE WHEN
su.isntuser=1 THEN 'Yes' ELSE 'No' END 'NT User',
CASE WHEN
su.issqluser=1 THEN 'Yes' ELSE 'No' END 'SQL User'
FROM sysusers su
INNER join sysmembers sm
ON su.uid = sm.memberuid
INNER join sysusers su2
ON sm.groupuid = su2.uid
INNER join master.dbo.syslogins sl
ON su.sid = sl.sid
WHERE su.NAME <> 'dbo'
SELECT * FROM #TempPermissions
SELECT DISTINCT 'EXEC sp_grantdbaccess ''' + UserName + '''' FROM #TempPermissions -- WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
SELECT 'EXEC sp_addrolemember ''' + Group_Name + ''', ''' + UserName + ''''
FROM #TempPermissions
--WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
DROP TABLE #TempPermissions
June 7, 2011 at 9:43 am
Create the temp table, use sp_MSForEachDB to insert into it from each database, then select from it after that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 9:48 am
i am weak in tsql...can u please tell me how to do that
June 7, 2011 at 9:59 am
CREATE TABLE #TempPermissions
(uid SMALLINT,
UserName VARCHAR(100),
Group_Name VARCHAR(100),
NT_Group VARCHAR(3),
NT_User VARCHAR(3),
SQL_USer VARCHAR(3)) ;
EXEC sp_MSForEachDB '
INSERT #TempPermissions
SELECT su.uid, sl.name, su2.name,
CASE WHEN
su.isntgroup=1 THEN ''Yes'' ELSE ''No'' END ''NT Group'',
CASE WHEN
su.isntuser=1 THEN ''Yes'' ELSE ''No'' END ''NT User'',
CASE WHEN
su.issqluser=1 THEN ''Yes'' ELSE ''No'' END ''SQL User''
FROM sysusers su
INNER join sysmembers sm
ON su.uid = sm.memberuid
INNER join sysusers su2
ON sm.groupuid = su2.uid
INNER join master.dbo.syslogins sl
ON su.sid = sl.sid
WHERE su.NAME <> ''dbo'';'
SELECT *
FROM #TempPermissions
SELECT DISTINCT
'EXEC sp_grantdbaccess ''' + UserName + ''''
FROM #TempPermissions
-- WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
SELECT 'EXEC sp_addrolemember ''' + Group_Name + ''', ''' + UserName + ''''
FROM #TempPermissions
--WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
DROP TABLE #TempPermissions
All I did was add in the "for each database" portion of this. I haven't tested or checked the latter part of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 10:13 am
its not giving any result
June 7, 2011 at 10:46 am
Every iteration done inside the "for each" proc is done on an independent thread so you need to use a global temp table or a permanent table. Try it like this:
IF OBJECT_ID(N'tempdb..##TempPermissions') > 0
DROP TABLE ##TempPermissions ;
GO
CREATE TABLE ##TempPermissions
(
uid SMALLINT,
UserName VARCHAR(100),
Group_Name VARCHAR(100),
NT_Group VARCHAR(3),
NT_User VARCHAR(3),
SQL_USer VARCHAR(3)
) ;
EXEC sys.sp_MSforeachdb
@command1 = N'
INSERT ##TempPermissions
SELECT su.uid, sl.name, su2.name,
CASE WHEN
su.isntgroup=1 THEN ''Yes'' ELSE ''No'' END ''NT Group'',
CASE WHEN
su.isntuser=1 THEN ''Yes'' ELSE ''No'' END ''NT User'',
CASE WHEN
su.issqluser=1 THEN ''Yes'' ELSE ''No'' END ''SQL User''
FROM ?..sysusers su
INNER join ?..sysmembers sm
ON su.uid = sm.memberuid
INNER join ?..sysusers su2
ON sm.groupuid = su2.uid
INNER join master.dbo.syslogins sl
ON su.sid = sl.sid
WHERE su.NAME <> ''dbo'';' ;
SELECT *
FROM ##TempPermissions ;
SELECT DISTINCT
'EXEC sp_grantdbaccess ''' + UserName + ''''
FROM ##TempPermissions ;
-- WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
SELECT 'EXEC sp_addrolemember ''' + Group_Name + ''', ''' + UserName + ''''
FROM ##TempPermissions ;
--WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
--DROP TABLE ##TempPermissions
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 11:28 am
opc.three (6/7/2011)
Every iteration done inside the "for each" proc is done on an independent thread so you need to use a global temp table or a permanent table. Try it like this:
IF OBJECT_ID(N'tempdb..##TempPermissions') > 0DROP TABLE ##TempPermissions ;
GO
CREATE TABLE ##TempPermissions
(
uid SMALLINT,
UserName VARCHAR(100),
Group_Name VARCHAR(100),
NT_Group VARCHAR(3),
NT_User VARCHAR(3),
SQL_USer VARCHAR(3)
) ;
EXEC sys.sp_MSforeachdb
@command1 = N'
INSERT ##TempPermissions
SELECT su.uid, sl.name, su2.name,
CASE WHEN
su.isntgroup=1 THEN ''Yes'' ELSE ''No'' END ''NT Group'',
CASE WHEN
su.isntuser=1 THEN ''Yes'' ELSE ''No'' END ''NT User'',
CASE WHEN
su.issqluser=1 THEN ''Yes'' ELSE ''No'' END ''SQL User''
FROM ?..sysusers su
INNER join ?..sysmembers sm
ON su.uid = sm.memberuid
INNER join ?..sysusers su2
ON sm.groupuid = su2.uid
INNER join master.dbo.syslogins sl
ON su.sid = sl.sid
WHERE su.NAME <> ''dbo'';' ;
SELECT *
FROM ##TempPermissions ;
SELECT DISTINCT
'EXEC sp_grantdbaccess ''' + UserName + ''''
FROM ##TempPermissions ;
-- WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
SELECT 'EXEC sp_addrolemember ''' + Group_Name + ''', ''' + UserName + ''''
FROM ##TempPermissions ;
--WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
--DROP TABLE ##TempPermissions
No, you don't. It's on the same connection, so it's got access to a normal temp table. I tested that part, I just didn't run the resulting script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 11:37 am
My fault. I knew the for each proc made use of EXEC and I did not know it would have access to temp tables declared outside the EXEC-scope. The real issue was the missing ?'s in your previous post.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 11:57 am
opc.three (6/7/2011)
The real issue was the missing ?'s in your previous post.
Now that I can easily believe! Won't be the first, nor the last, time that I missed those. Too used to using my own cursors for this, instead of Microsoft's.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2011 at 2:02 am
any help to get the above query to run on all databases
June 8, 2011 at 7:27 am
GSquared (6/7/2011)
opc.three (6/7/2011)
Every iteration done inside the "for each" proc is done on an independent thread so you need to use a global temp table or a permanent table. Try it like this:
IF OBJECT_ID(N'tempdb..##TempPermissions') > 0DROP TABLE ##TempPermissions ;
GO
CREATE TABLE ##TempPermissions
(
uid SMALLINT,
UserName VARCHAR(100),
Group_Name VARCHAR(100),
NT_Group VARCHAR(3),
NT_User VARCHAR(3),
SQL_USer VARCHAR(3)
) ;
EXEC sys.sp_MSforeachdb
@command1 = N'
INSERT ##TempPermissions
SELECT su.uid, sl.name, su2.name,
CASE WHEN
su.isntgroup=1 THEN ''Yes'' ELSE ''No'' END ''NT Group'',
CASE WHEN
su.isntuser=1 THEN ''Yes'' ELSE ''No'' END ''NT User'',
CASE WHEN
su.issqluser=1 THEN ''Yes'' ELSE ''No'' END ''SQL User''
FROM ?..sysusers su
INNER join ?..sysmembers sm
ON su.uid = sm.memberuid
INNER join ?..sysusers su2
ON sm.groupuid = su2.uid
INNER join master.dbo.syslogins sl
ON su.sid = sl.sid
WHERE su.NAME <> ''dbo'';' ;
SELECT *
FROM ##TempPermissions ;
SELECT DISTINCT
'EXEC sp_grantdbaccess ''' + UserName + ''''
FROM ##TempPermissions ;
-- WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
SELECT 'EXEC sp_addrolemember ''' + Group_Name + ''', ''' + UserName + ''''
FROM ##TempPermissions ;
--WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
--DROP TABLE ##TempPermissions
No, you don't. It's on the same connection, so it's got access to a normal temp table. I tested that part, I just didn't run the resulting script.
above query is working ...but i just need to append a USE DBNAME for the particular access...pls help
June 8, 2011 at 7:50 am
Add "USE [?];" at the begginning of the string. That's what sp_MSForEachDB uses to plug in the database name.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2011 at 8:35 am
You do not need the USE because I fully-qualifed all the table names. The problem is with the SELECTs at the bottom. Try this version, I added a column to the temp table to store the DB name so we can output fully-qualified calls to the system procs that grant access add grant role members.
IF OBJECT_ID(N'tempdb..##TempPermissions') > 0
DROP TABLE ##TempPermissions ;
GO
CREATE TABLE ##TempPermissions
(
uid SMALLINT,
db sysname,
UserName VARCHAR(100),
Group_Name VARCHAR(100),
NT_Group VARCHAR(3),
NT_User VARCHAR(3),
SQL_USer VARCHAR(3)
) ;
EXEC sys.sp_MSforeachdb
@command1 = N'
INSERT ##TempPermissions
SELECT su.uid, ''?'', sl.name, su2.name,
CASE WHEN
su.isntgroup=1 THEN ''Yes'' ELSE ''No'' END ''NT Group'',
CASE WHEN
su.isntuser=1 THEN ''Yes'' ELSE ''No'' END ''NT User'',
CASE WHEN
su.issqluser=1 THEN ''Yes'' ELSE ''No'' END ''SQL User''
FROM ?..sysusers su
INNER join ?..sysmembers sm
ON su.uid = sm.memberuid
INNER join ?..sysusers su2
ON sm.groupuid = su2.uid
INNER join master.dbo.syslogins sl
ON su.sid = sl.sid
WHERE su.NAME <> ''dbo'';' ;
SELECT *
FROM ##TempPermissions ;
SELECT DISTINCT
'EXEC ' + db + '.sys.sp_grantdbaccess ''' + UserName + ''''
FROM ##TempPermissions ;
-- WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
SELECT 'EXEC ' + db + '.sys.sp_addrolemember ''' + Group_Name + ''', ''' + UserName + ''''
FROM ##TempPermissions ;
--WHERE NT_USER = 'Yes' or NT_Group = 'Yes'
--DROP TABLE ##TempPermissions
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply