Stored Procedures - what have they done??
-
We have inherited 3 small in-house databases where there are a number of issues. Can I ask for your assistance on the following?
1.Some stored procedures in the databases are called sp_<name>. SQL Books on line says that this is not a good idea but what specific effect will this have?
2.Is there a graphical tool that I can use to see exact what the stored procedures are or are attempting to do? If you examine the code, some of it is in red which is worrying.
3.The original programmer gave his colleagues all the fixed server roles, before he left. However the databases are moving to a location where they cannot have access to 'sa' and certainly not leave the password to 'sa' as blank. Is there a particular method to see which user logins 'own' roles/permissions on the tables/stored procedures/DTS packages?
These small databases are causing us more trouble than the rest of the databases i.e. I thought I had already posted this thread but it appears that that thread also went wrong. If I posted this thread twice, please accept my apologies.
Madame Artois
-
1. When a procedure is named sp_<name> (or dbo.sp_<name> ), SQL Server will search for it in the Master database before searching in the current database. If you have a server with many users beating on it, that small trip to master for each procedure execution can start to add up. If the procedure is called with the database name (Northwind.dbo.sp_myproc, Northwind..sp_myproc, myserver.Northwind.dbo.sp_myproc), then this trip to master does not occur.
2. A graphical tool to see what procedures are trying to do: um, yes and no. Use Query Analyzer to look at the code. You can see graphical query plans by highlighting specific SQL in Query Analyzer (to see a procedure plan, type an exec command for it with sample parameters, like EXEC dbo.sp_myproc 100, 'Foo' and highlight that) and then hit Ctrl+L. What is displayed will probably go over your head at this point, thought. The important thing is that the code in red does not mean there is a problem. It's just color-coding to indicate text. If you look, you should see that all the red text has single-quotes around it:
EXEC dbo.sp_myproc 100, 'Foo'
3. You can see in Enterprise Manager, Query Analyzer, and most other tools, that each object (stored procedure, table, view, function) is displayed with a two-part name, such as dbo.sp_myproc or eddie.Foo . The object owner (specifically the login that owns the object) preceeds the object: dbo owns dbo.sp_myproc, Eddie owns Foo. If you look at the users for each database in Enterprise Manager, you can examine individual users to see if any of them are Aliased to a different user. For example, I may log in to a server via Windows Authentication as MIRAITECH\Eddie, then be aliased as dbo when I log in to the Northwind database.
By default, the 'sa' login is aliased to dbo in every database.
Any user granted membership in the db_owner and database security admin group in a database can control permissions. You do not need to re-assign ownership in order to control access.
Good luck with the server move and security fixes.
-Eddie
Eddie Wuerch
MCM: SQL -
If you have text in red (which means it is a quoted string) that looks like T-SQL code, the stored procedures are probably using dynamic SQL. This means they are creating commands at run time and executing them with EXEC (@string) or sp_executesql. No static analysis such as displaying the execution plan will show you what these commands do.
You could use SQL Profiler to trace an execution of these procedures and capture the SP:Statement Completed event to see what the actual commands are.
-
For #1, I go into detail in this article:
Basically, a [COMPILE] lock gets put on the object every time it is executed. This is because of the looking in master and not finding it, so it has to look further. That [COMPILE] lock is exclusive, meaning only one database connection can use the stored procedure at a time. That serializes the stored procedure. Also, you're getting the short procedure cache scan and then the longer, more intensive procedure cache scan each time the stored procedure is called as opposed to just the initial short procedure cache scan. That impairs performance, too.
K. Brian Kelley
@kbriankelley -
Thanks for all the help, guys. I've asked the Development Manager to sort the sp_ procedures out. I've also given the programmers a copy of the article by 'bkelley' which explains beautifully why they need to do it.
No doubt there will be more, I'll keep you posted.
Madame Artois
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply