2013/07/28

Find Stored Procedures and Functions That Reference Certain Tables

During the data discovery process on a database that I have inherited from another designer, I often have the need to determine all the stored procedures that interact with a certain table. For instance, I may need to look up all the stored procedures that insert records into a certain table. Or all the stored procedures that look up a certain view. Here is a code snippet that allows me to plug in the name of the table/view, and returns all the code objects (stored procedures, functions, triggers etc) that interact with that table/view. It returns the object type, the name of the object, and the code within that object broken up into more than one Code Sequence if the code is lengthy enough (longer than 4000 characters). You could, of course, also plug in the name of a code object to return other code objects that look up your searched object, for instance to find all the stored procedures that use a certain function.

Just replace the "search_term" in the code snippet with the name of the table/view/stored procedure/function etc that you want to search on.

select distinct
ObjectType = o.type_desc
, ObjectName = o.name
, CodeSequence = c_display.colid
, Code = c_display.[text]
from sys.objects o
inner join sys.syscomments c_search
on c_search.id = o.[object_id]
inner join sys.syscomments c_display
on c_display.id = o.[object_id]
where o.type_desc not in ('INTERNAL_TABLE','SERVICE_QUEUE','SYSTEM_TABLE')
and c_search.[text] like '%search_term%'
order by 1, 2, 3

2013/07/17

Delete All Database User Accounts for a Given Server Login

Ever had the need to remove all database-level user accounts from SQL Server for a given server level login? Say an employee leaves your organization, and you need to remove her account from all databases. Do you do this manually? Do you use the GUI in SSMS? Do you script this out? What if the database level accounts do not have the same name as the login? What if the account name is different in every database? What if there are hundreds of databases on your server, making this a tedious exercise?

Fear not, here is a script that you can run on SQL Server that will drop all database-level user accounts for a specified login. Thanks to Jason Strate for this article which inspired me to create this script. Just set the variable @LoginName to the login for which you want accounts deleted, and execute.


/*===================================================================================================
2013/07/15 hakim.ali@SQLzen.com

        SQL Server 2005 and higher.

        Script to delete (drop) all database level user accounts for a given server login from all
        databases on a database server. This will drop users even if they have a different name from
        the server login, so long as the two are associated. This will not drop users from a database
        where there are schemas/roles owned by the user.
       
        ** USE ONLY IF YOU ARE AN EXPERIENCED DBA FAMILIAR WITH SERVER LOGINS, DATABASE USERS, ROLES,
        SCHEMAS ETC. USE AT YOUR OWN RISK. BACK UP ALL DATABASES BEFORE RUNNING. **
=====================================================================================================*/
use [master]
go

--------------------------------------------------------------------------------------
-- Set the login name here for which you want to delete all database user accounts.
declare @LoginName nvarchar(200); set @LoginName = 'LOGIN_NAME_HERE'
--------------------------------------------------------------------------------------

declare @counter int
declare @sql nvarchar(1000)
declare @dbname nvarchar(200)

-- To allow for repeated running of this script in one session (for separate logins).
begin try drop table #DBUsers end try begin catch end catch

----------------------------------------------------------
-- Temp table to hold database user names for the login.
----------------------------------------------------------
create table #DBUsers
(                       ID          int identity(1,1)
                ,       LoginName   varchar(200)
                ,       DB          varchar(200)
                ,       UserName    varchar(200)
                ,       Deleted     bit
)

-- Add all user databases.
insert into #DBUsers
(                       LoginName
                ,       DB
                ,       Deleted
)
select                  @LoginName
                ,       name
                ,       1
from            sys.databases
where           name not in ('master','tempdb','model','msdb')
and             is_read_only = 0
and             [state] = -- online
order by        name

----------------------------------------------------------
-- Add database level users (if they exist) for the login.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
        set @dbname = (select db from #DBUsers where ID = @counter)
        set @sql = '
        update          temp
        set             temp.UserName = users.name
        from            sys.server_principals                      as logins
        inner join      [' + @dbname + '].sys.database_principals  as users
                        on users.sid = logins.sid
                        and logins.name = ''' + @LoginName + '''
        inner join      #DBUsers                                   as temp
                                on temp.DB = ''' + @dbname + ''''

        exec sp_executesql @sql
       
        set @counter = @counter + 1
end

-- Don't need databases where a login-corresponding user was not found.
delete          #DBUsers
where           UserName is null

----------------------------------------------------------
-- Now drop the users.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
        select  @sql = 'use [' + DB + ']; drop user [' + UserName + ']'
        from    #DBUsers
        where   ID = @counter

        --select @sql
        begin try exec sp_executesql @sql end try begin catch end catch
        set @counter = @counter + 1
end

----------------------------------------------------------
-- Report on which users were/were not dropped.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
        set @dbname = (select db from #DBUsers where ID = @counter)
        set @sql = '
        update          temp
        set             temp.Deleted = 0
        from            sys.server_principals                       as logins
        inner join      [' + @dbname + '].sys.database_principals   as users
                        on users.sid = logins.sid
                        and logins.name = ''' + @LoginName + '''
        inner join      #DBUsers                                    as temp
                                on temp.DB = ''' + @dbname + ''''

        exec sp_executesql @sql
       
        set @counter = @counter + 1
end

-- This shows the users that were/were not dropped, and the database they belong to.
if exists (select 1 from #DBUsers)
begin
        select                  LoginName
                        ,       [Database]     = DB
                        ,       UserName       = UserName
                        ,       Deleted        = case Deleted when 1 then 'Yes' else 'No !!!!!!' end
        from            #DBUsers
        order by        DB
end
else
begin
        select [No Users Found] = 'No database-level users found on any database for the login "' + @LoginName + '".'
end

/*===================================================================================================
Not automatically dropping the login. If there are database level users that were not dropped,
dropping the login will create orphaned users. Enable at your discretion.
=====================================================================================================*/
/*
set @sql = 'drop login [' + @LoginName + ']'
exec sp_executesql @sql
*/



2013/07/03

Tables and Clustered Indexes Part 2

A little while ago I wrote a piece on why a table does not so much have a clustered index as a table is a clustered index*. Here's a follow up with some SQL code that further strengthens that assertion, at least the way it is implemented in SQL Server.

Let's start by creating a very simple table and inserting one record into it:

create table dbo.test (id int)

insert into dbo.test (id) select 1

Note that this table is currently a heap, it does not have any indexes of any type. Verify that the table has one record:

select * from dbo.test


Here is the execution plan for this select:


As expected, we have a table scan on the heap. Now let's add a non-clustered index to this table on the id column: 

create nonclustered index NCIndex_test on dbo.test (id)

Run the select statement again, and you will see the same result again, as expected. The execution plan, of course, is different. Now the query uses the non-clustered index instead of the table scan (I won't get into seeks vs scans here, different conversation).


Now let's try disabling (not dropping) the non-clustered index:

alter index NCIndex_test on dbo.test disable

Once again, run the select statement. We now see the same result and execution plan as in our first select statement above. The index is ignored, and the table is treated like a heap, resulting in a table scan. Again, so far all of this is as expected.

Now, let's repeat the above exercise with a clustered index. To keep everything else the same between the two exercises, I will drop and recreate the table.

drop table dbo.test

create table dbo.test (id int)

insert into dbo.test (id) select 1

Now, running our select will again show us the table scan:

select * from dbo.test



Let us now create the clustered index:

create clustered index CIndex_test on dbo.test (id)

Running our select statement again, here's what our execution plan looks like with the clustered index:

select * from dbo.test


Now let's disable (not drop) the clustered index:

alter index CIndex_test on dbo.test disable

Now if you run the select statement, what do you expect to see? I would have expected to see the table treated as a heap, and a table scan in the execution plan. It seems reasonable to assume that clustered table minus clustered index equals heap, right?

select * from dbo.test


Apparently, not so. Disabling the non-clustered index causes the query to treat the table like a heap, but disabling the clustered index does not have the same effect. Disabling the clustered index disables the table itself.

Thus, when you add a clustered index to a table, the table becomes the clustered index*. A table does not so much have a clustered index as a table is a clustered index.


* There is much more to a clustered index, including nodes, leaves, a root, a b-tree structure etc. This is a simplification to help illustrate the point. As an aside, if you drop the clustered index (drop index CIndex_test on dbo.test), the table goes back to being a heap and returns results from the select statement. Here is what Books Online/MSDN has to say on the subject: "Disabling an index prevents user access to the index, and for clustered indexes to the underlying table data... Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt."