June 10, 2011 at 6:50 am
I need a procedure to cycle through every database on the server and display the user permissions for a user. This code only displays the user roles for one database and when i move to the next database the output is the same as the first.
--Cycles through each database to output user permissions
DECLARE c_dbnames CURSOR FOR (SELECT name FROM sysdatabases)
DECLARE @DBName varCHAR(100)
DECLARE @DatabaseUserName [sysname]
DECLARE @MsgStatement [varCHAR](8000)
DECLARE@DatabaseUserID [smallint]
DECLARE@RoleName [varCHAR](8000)
set @DatabaseUserName = 'kbenevit'
OPEN c_dbnames
FETCH NEXT FROM c_dbnames INTO @DBName
while(@@FETCH_STATUS = 0)
BEGIN
SELECT
@DatabaseUserID = [sysusers].[uid]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
--Outputs various information for the login name
SET @MsgStatement = 'Security Permissions For Login: ' + CHAR(13) + CHAR(9) + @DatabaseUserName + CHAR(13) + CHAR(13) +
'Database: ' + CHAR(13) + CHAR(9) + @DBName + CHAR(13) + CHAR (13) + 'User Permissions:'
PRINT @MsgStatement
DECLARE c_users CURSOR
FOR SELECT [name] FROM [dbo].[sysusers]
WHERE [uid] IN
(
SELECT [groupuid] FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
--Cycles through the user permissions for the login name
OPEN c_users
FETCH NEXT FROM c_users INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MsgStatement = CHAR(9) + @RoleName
PRINT @MsgStatement
FETCH NEXT FROM c_users INTO @RoleName
END
SET @MsgStatement = '---------------------------------------------------------' + CHAR(13)
PRINT @MsgStatement
CLOSE c_users
DEALLOCATE c_users
FETCH NEXT FROM c_dbnames into @DBName
END
CLOSE c_dbnames
DEALLOCATE c_dbnames
END
June 10, 2011 at 7:55 am
I see the cursor cycling through the database names, but the query doesn't do anything with that so far as I can tell. You aren't actually switching databases just because a variable is set to the name of a database. Or am I missing something?
- 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 10, 2011 at 8:00 am
I think you're right. How can i cycle through the database and output information from each database?
June 10, 2011 at 8:01 am
The easiest way is make the query into dynamic SQL, plug in the database name as part of a three-part name for the tables, and execute it that way.
- 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 10, 2011 at 8:14 am
Thanks
June 10, 2011 at 8:15 am
simplier: EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 10, 2011 at 1:10 pm
Crispin Proctor (6/10/2011)
simplier: EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'
Considering the query and return results he's trying for, I discounted the system proc. The input command would be complex to build, just because of nested single-quotes issues.
Also, all that proc does is cursor through sys.databases, with some bells and whistles added. It's no more efficient than building your own cursor, and less flexible in its Select clause for the cursor.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply