June 7, 2007 at 10:31 am
Greetings everyone,
I recently wrote this little script to find out why certain users needed 'data_writer' access to some of our databases. There are some areas that can certainly be improved but I wanted to share with you all and get some more sets of eyes on the code to see if I might be missing anything. Reason I say that is because for what I'm seeing my results for the users I'm investigating I didn't get the results I was expecting. Not to say that my assumptions about the users are always right....but I just expected more...
Let me know what you think!
-- Drop Old Tables
if object_id(N'protect', 'U') is not null
drop table protect
GO
if object_id(N'procs', 'U') is not null
drop table protect
GO
--Create new tables
create table procs (
[Database] varchar(100),
[Schema] varchar(100),
[Name] varchar(100),
[Type] varchar(100),
Created datetime,
Modified datetime
)
create table protect(
id int identity(1,1),
[Database] varchar(256),
[Type] varchar(256),
Owner varchar(100),
Object varchar(256),
Grantee varchar(256),
Grantor varchar(256),
ProtectType varchar(256),
Action varchar(256),
[Column] varchar(256)
)
--Vars
declare
@db varchar(256),
@sql varchar(4000),
@br char(10),
@user varchar(256)
select
@br = char(10),
@user = 'bendover'
--Fill procs table *sp_SearchText searches the text of the stored procs
insert into procs exec sp_SearchText 'update'
insert into procs exec sp_SearchText 'insert'
--Find object permissions for user
declare curDatabases cursor for
select distinct name from master..sysdatabases
open curDatabases
fetch next from curDatabases into @db
while @@fetch_status=0
begin
set @sql = 'use ' + @db + @br
set @sql = @sql + 'insert into master..protect (Owner, Object, Grantee, Grantor, ProtectType, Action, [Column]) ' + @br
set @sql = @sql + 'EXEC sp_helprotect @username=''' + @user + ''', @permissionarea=''O,S''' + @br
set @sql = @sql + 'update master..protect set [Database] = ''' + @db + ''' where [Database] is null' + @br
print('/***** ' + @db + ' *****/'+ @br)
print(@sql)
exec(@sql)
fetch next from curDatabases into @db
end
close curDatabases
deallocate curDatabases
--Return matching results
select
a.[Database],
Object,
Grantee,
ProtectType,
Action
from
master..protect a
inner join master..procs b on
a.Object = b.Name
Ben Sullins
bensullins.com
Beer is my primary key...
June 7, 2007 at 1:24 pm
I'd work in sp_msforeachdb as well as my favorite means to search all procedures:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%INSERT%'
OR ROUTINE_DEFINITION LIKE '%UPDATE%'
i.e...
exec sp_msforeachdb 'INSERT #my_procedures (db_nm, procedure_nm)
SELECT ''?'' as db_nm, SPECIFIC_NAME FROM ?.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ''%INSERT%''
OR ROUTINE_DEFINITION LIKE ''%UPDATE%''
'
June 7, 2007 at 2:18 pm
Aaron,
You have to be careful when using the schema views for scanning the SP code for specific strings. The view shows only first 4000 characters of the SP code. So if your SP code is longer then that you will not find all instances of what you looking for.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
June 10, 2007 at 3:43 pm
It looks like this could help me but ... I don't have and can't find sp_SearchText. Can someone post the difinition of it please
Regards
Karl
June 11, 2007 at 9:26 am
Karl,
sp_SearchText is a homegrown stored procedure that Ben uses to search for strings in stored procedures. It basically the same thing that Aaron's code does. Here's another way to do it:
-- note: the reason this joins to a second instance of syscomments is to find cases where
--the string being searched for 'spans' two rows in syscomments. (the text of the stored
--procedure is whacked into 'chunks' and stored in the .text column of multiple rows in
--syscomments)
select distinct
so.name from sysobjects so
join syscomments sc on sc.id = so.id
left outer join syscomments sc1 on sc1.id = sc.id and sc1.colid = sc.colid + 1
where so.type = 'p'
and ( sc.text like '%string%'
or right(sc.text,500) + left(isnull(sc1.text,''),500) like '%string%')
order by so.name
Greg
Greg
June 11, 2007 at 10:16 am
Here's the code for the sp_SearchText
if object_id('sp_SearchText') is not null
drop proc sp_SearchText
GO
-- exec sp_SearchText 'EIS'
create proc sp_SearchText
@search nvarchar(1000)
as
create table #results (
[Database] nvarchar(128),
[Schema] nvarchar(128),
[Name] nvarchar(128),
[Type] nvarchar(20),
[Created] datetime,
[Modified] datetime
)
declare @db nvarchar(128)
declare @sql nvarchar(1000)
declare curDatabases cursor for
select distinct name from master..sysdatabases
open curDatabases
fetch next from curDatabases into @db
while @@fetch_status = 0
begin
set @sql = '
insert into #results
select
ROUTINE_CATALOG[Database],
ROUTINE_SCHEMA[Schema],
ROUTINE_NAME[Name],
ROUTINE_TYPE[Type],
CREATED[Created],
LAST_ALTERED[Altered]
from
['+ @db +'].INFORMATION_SCHEMA.ROUTINES
where
ROUTINE_DEFINITION like N''%' + @search + '%''
'
exec(@sql)
fetch next from curDatabases into @db
end
close curDatabases
deallocate curDatabases
select * from #results
GO
Ben Sullins
bensullins.com
Beer is my primary key...
June 11, 2007 at 10:41 am
The problem might be in your sp_SearchText.
As I mentioned above you should not use the ROUTINES view to search the text of a SP. (To see the code INFORMATION_SCHEMA.ROUTINES uses - go to the master database and check out the ROUTINES view)
Instead use the code provided by Greg above.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
June 11, 2007 at 2:04 pm
Greg,
I tried updating my sp_SearchText with your query above but am getting some interesting errors. Any idea's?
declare
@search nvarchar(50)
set @search = 'insert'
drop table #results
create table #results (
[Database] nvarchar(128),
[Schema] nvarchar(128),
[Name] nvarchar(128),
[Type] nvarchar(20),
[Created] datetime,
[Modified] datetime
)
declare @db nvarchar(128)
declare @sql nvarchar(1000)
declare curDatabases cursor for
select distinct name from master..sysdatabases
open curDatabases
fetch next from curDatabases into @db
while @@fetch_status = 0
begin
set @sql = '
insert into #results([Database], [Name])
select
''' + @db + ''',
so.name
from
[' + @db + '].dbo.sysobjects so
inner join [' + @db + '].dbo.syscomments sc on
sc.id = so.id
left join [' + @db + '].dbo.syscomments sc1 on
sc1.id = sc.id and
sc1.colid = sc.colid + 1
where
so.type = ''p'' and
(
sc.text like ''%' + @search + '%'' or
right(isnull(sc.text,''''),500) + left(isnull(sc1.text,''''),500) like ''%' + @search + '%''
)'
print(@sql)
exec(@sql)
fetch next from curDatabases into @db
end
close curDatabases
deallocate curDatabases
select * from #results
Ben Sullins
bensullins.com
Beer is my primary key...
June 12, 2007 at 10:54 pm
Thanks Ben,
I've already used the sp_searchtext in anger.
Karl
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply