Find SQL Server 2000 table level permissions programmatically 1

[wbodger]

I am trying to generate a report with security information down to the table level. I can get the db permissions, but cannot find where I could pull table or even column level permissions. Anybody?

Thanks,
wb

 

[wbodger]

For instance, I have this code to iterate thru all of my databases (again SQL Server 2000), place the information in a temp table and then join that to syslogins to see what logins are nto mapped to anything

USE MASTER
GO

BEGIN

IF  EXISTS (SELECT * FROM dbo.sysobjects 
			WHERE id = OBJECT_ID(N'[dbo].#TUser') 
					AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE #TUser

CREATE TABLE #tuser (
 DBName VARCHAR(50),
 UserName SYSNAME,
 GroupName SYSNAME NULL,
 LoginName SYSNAME NULL,
 uid INT,
 sid VARBINARY(85))

INSERT INTO #TUser
EXEC sp_MSForEachdb
 '
 SELECT ''?'' as DBName,
	u.name As UserName,
	CASE 
		WHEN (r.uid IS NULL) THEN ''public''
		ELSE r.name
		END  AS GroupName,
	l.name AS LoginName,
	u.uid,
	u.sid
 FROM ?.dbo.sysUsers u
	LEFT JOIN (?.dbo.sysMembers m
			 JOIN ?.dbo.sysUsers r
				ON m.memberuid = r.uid)
	ON m.memberuid = u.uid
	LEFT JOIN dbo.sysLogins l
	ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
 /*and u.name like ''tester''*/
 ORDER BY u.name
 '

SELECT syslogins.loginname, #Tuser.*
FROM syslogins left outer join #TUser on syslogins.loginname=#TUser.username
ORDER BY syslogins.loginname, #TUser.DBName,
 #TUser.UserName,
 #TUser.GroupName

DROP TABLE #TUser
END

I now see there is a sysusers table for each table, so I should be able to iterate thru each table with sp_msforeachtable left joined to my above output to show objects at the table level that do not have permissions that they have on the database?

Any thoughts on this?

Thanks for your help!

wb

 

[gmmastros]

I honestly haven't got much time right now to dig in to this too deeply. However, I suggest you take a look at the [!]sysprotects[/!] and/or [!]syspermissions[/!] tables. I suspect the information you are looking for is stored there.

http://msdn.microsoft.com/en-us/library/aa260449(v=sql.80).aspx

This can get a little complicated because users can be added to a database role and permissions can be set on the role, or you can also set permissions on each login.

Complicated stuff. Be careful.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Thanks, George. I will look around there. I do not want to change or set anything there, I just want to look at it.

wb

 

[wbodger]

This gets me pretty close. It did not like it when I tried to use sp_msforeachdb

use dbName
SELECT sysusers.NAME AS login,
	sysobjects.NAME AS object,
	syscolumns.NAME AS tCOLUMN,
	case action
		when 26 then 'References'
		when 178 then 'Create Function'
		when 193 then 'Select'
		when 195 then 'Insert'
		when 196 then 'Delete'
		when 197 then 'Update'
		when 198 then 'Create Table'
		when 203 then 'Create Database'
		when 207 then 'Create View'
		when 222 then 'Create Procedure'
		when 224 then 'Execute'
		when 228 then 'Backup Database'
		when 233 then 'Create Default'
		when 235 then 'Backup Log'
		when 236 then 'create Rule'
	end as action
FROM [dbo].[sysprotects]
	JOIN [dbo].[sysobjects] ON [sysprotects].[id]=[sysobjects].[id]
	JOIN [dbo].[sysusers] ON [sysprotects].[uid]=[sysusers].[uid]
	JOIN dbo.syscolumns ON sysobjects.id=syscolumns.id
WHERE sysobjects.type IN ('U','V')
ORDER BY login, object, tcolumn

wb

 

[gmmastros]

It did not like it when I tried to use sp_msforeachdb

The way I usually do this is... I select all the text and replace single-quote with 2 single-quote. I then add a single quote to the beginning and end. I add sp_msforeachdb at the beginning and finally add the ? to the string.

For example:

Create 
Table	#Temp(
			DatabaseName VarChar(200), 
			LoginName VarChar(200), 
			ObjectName VarChar(200), 
			ColumnName VarChar(200),
			Action VarChar(200))

exec sp_msforeachdb
'
 Insert Into #Temp(DatabaseName, LoginName, ObjectName, ColumnName, Action)
 SELECT ''?'' As DatabaseName,
    sysusers.NAME AS login,
    sysobjects.NAME AS object,
    syscolumns.NAME AS tCOLUMN,
    case action
        when 26 then ''References''
        when 178 then ''Create Function''
        when 193 then ''Select''
        when 195 then ''Insert''
        when 196 then ''Delete''
        when 197 then ''Update''
        when 198 then ''Create Table''
        when 203 then ''Create Database''
        when 207 then ''Create View''
        when 222 then ''Create Procedure''
        when 224 then ''Execute''
        when 228 then ''Backup Database''
        when 233 then ''Create Default''
        when 235 then ''Backup Log''
        when 236 then ''create Rule''
    end as action
FROM [?].[dbo].[sysprotects]
    JOIN [?].[dbo].[sysobjects] ON [sysprotects].[id]=[sysobjects].[id]
    JOIN [?].[dbo].[sysusers] ON [sysprotects].[uid]=[sysusers].[uid]
    JOIN [?].dbo.syscolumns ON sysobjects.id=syscolumns.id
WHERE sysobjects.type IN (''U'',''V'')
ORDER BY login, object, tcolumn'

Select * From #Temp
Drop Table #Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

I just had to add the 'use ?' before the insert and it worked great! Looks like what I was trying, except I did not use a temp table. Well, anyway, Thank you very much, definitely learning a lot over these past couple of weeks and greatly appreciate the help!!

Willie