tag:blogger.com,1999:blog-83536068919167592022024-03-19T14:23:02.677-04:00Useful TSQL SyntaxSQL Server 2000, SQL Server 2005, tsql, transact sql, MS SQL, Microsoft SQL ServerSQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-8353606891916759202.post-10561449082691038292008-09-04T17:10:00.002-04:002008-09-04T17:56:54.091-04:00Is the file there?I was recently asked to help verify that files actually existed in the locations specified in a field of a table. That is that the database has a table that contains a field that contains a file path and a file. My task (since I chose to accept it) was to validate that the database value actually pointed to a file in the OS.<br /><br />A rather undocumented extended stored procedure continues to exist in SQL Server 2005 (xp_fileexist). I decided to use it since this task was not for application functionality but simply an ad-hoc query to help troubleshoot an issue. Microsoft discourages the use of undocumented syntax because it does not guarantee support for or existence of that syntax in the future. But for this situation, it was ok.<br /><br />So basically the thing is used like this:<br /><br />DECLARE @result INT<br />EXECUTE xp_fileexist 'c:\autoexec.bat', @result OUTPUT<br /><br />I do suggest you use a OUTPUT variable in this case, since you are checking more than a single file. XP_fileexist can also be used without the OUTPUT variable which will return a result set like this:<br /><br />File Exists | File is a Directory | Parent Directory Exists <br />----------- | ------------------- | ------------------<br />1 | 0 | 1<br /><br /><br />I however had a list of paths I needed to loop through so first I gathered the relevant data into a temp table. I created variables to hold a single file path value and also an INT OUTPUT variable to return the result (1= exists/ 0 = not exists).<br /><br />Within the loop, I then update the temp table for each file path so I could report back which file paths were there vs not. Last is just a SELECT from the temp table I just updated (the report).<br /><br /><br />use PoeticSQL_BlogEntries<br />go<br /><br />SET NOCOUNT ON<br />--drop table #temp1<br /><br />--dataset<br />SELECT uri, 0 as exists1<br />INTO #temp1<br />FROM Cache with (nolock) <br />WHERE exportmode='Print' <br /> and createdate > '09/04/2008' <br /> and uri like '%223454%'<br /><br />--variables <br />DECLARE @URI varchar(2000) --to hold currently searched file path<br />DECLARE @iFileExists int --to hold the return value from the XP<br /><br />--initialize the varibale<br />SELECT @URI = '' + min(cachefileuri) + '' FROM #temp1<br /><br />--THE Loop<br />WHILE @URI IS NOT NULL<br />BEGIN<br /><br />EXECUTE master..xp_fileexist @URI , @iFileExists OUTPUT<br /><br />UPDATE #temp1<br />SET exists1 = @iFileExists<br />WHERE cachefileuri = @URI<br /><br />--get next record<br />SELECT @URI = min(cachefileuri) FROM #temp1 WHERE cachefileURI > @URI<br />END<br /><br />--report<br />SELECT * FROM #temp1 <br /><br />--clean up<br />DROP TABLE #temp1<br /><br />There are newer solutions to this problem. Some use the OACreate and OADestroy objects which require special permissions and another interesting one I have seen but not tested myself is master..sp_MSget_file_existence.<br /><br />Good Luck and Happy TSQLing<br />SQLPoetSQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com0tag:blogger.com,1999:blog-8353606891916759202.post-9093951465367978772008-01-18T12:08:00.002-05:002008-09-08T21:05:06.795-04:00The transaction log for database 'PoeticSQL_BlogEntries' is fullIf you have (intentionally or inadvertently) set <br />your recovery level to FULL, your SQL Server transaction <br />logs will not be cleared of inactive transactions unless a <br />transaction log backup is performed. This is not the case <br />for database transaction logs whose recovery models <br />are set to SIMPLE. When the log needs more space, it will <br />grow automatically by the percent or number of MB specified*. <br />If not or if the file has already grown to the size of available <br />space on the drive, you could end up with the problem for<br />which this post it titled.<br /><br />If your company is like the one for which I work, it may have <br />databases that are 'in transition' to becoming <br />full-fledged 9.0 version databases but are currently <br />in 8.0 compatibilty mode. If that is true for you, you <br />may find that the reporting feature on the Summary screen <br />of Management Studio does not show you the Disk Usage <br />report which is useful for troubleshooting this sort of <br />thing.<br /><br /><strong>What to do?</strong><br />SQL Server 2005 might give you a hint and tell you to <br />check the reusability status of the log in sys.databases.<br /><br /> SELECT log_reuse_wait_desc, [name] <br /> FROM sys.databases<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgq82cmOP29MggTeasxmuVMUiR_6tGtBKZcxSv40pR9zNNBaRFyx9bBCWKRLC5V_RdXrTYjyTyPGbMeXIs8NqanH7zqaGm0jEBgwOTLeDpulR_tASggOQkypnBEtESYoGEyKps8rBZ_Kws/s1600-h/untitled2.bmp"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgq82cmOP29MggTeasxmuVMUiR_6tGtBKZcxSv40pR9zNNBaRFyx9bBCWKRLC5V_RdXrTYjyTyPGbMeXIs8NqanH7zqaGm0jEBgwOTLeDpulR_tASggOQkypnBEtESYoGEyKps8rBZ_Kws/s320/untitled2.bmp" border="0" alt=""id="BLOGGER_PHOTO_ID_5156869195105095154" /></a><br /><br />The resultset above indicates that my transaction log <br />is waiting to be backed up and that is the only thing <br />blocking the reuse of space within the log. <br /><br />But this still doesn't answer the question about <br />Disk Usage as it pertains to the transaction log. SQL <br />Server has a command that you can run to see the current <br />size of the transaction logs and how much space is <br />currently being utilized. The command is <br />DBCC SQLPERF(logspace). <br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNh6yM55seaT9gP9JdUHSF3gPAU6lerD4ye2tx53-eFn4DZL5gXIXcM2yicZ2r4lfEXvhswI4Axe3cDbAo6NKpkXNog-xByHQQJwQhs1RKdcw_Og_TPB23ozhhyOolKYyK9TLDpAaQUVw/s1600-h/ResultSet.bmp"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNh6yM55seaT9gP9JdUHSF3gPAU6lerD4ye2tx53-eFn4DZL5gXIXcM2yicZ2r4lfEXvhswI4Axe3cDbAo6NKpkXNog-xByHQQJwQhs1RKdcw_Og_TPB23ozhhyOolKYyK9TLDpAaQUVw/s320/ResultSet.bmp" border="0" alt=""id="BLOGGER_PHOTO_ID_5156868404831112674" /></a><br /><br />If you run this command at different times you will see the <br />Log Space Used (%) fluctuate depending on the activity in <br />your database, the recovery model set and whether you are <br />running transaction log backups (which clear out the inactive <br />transactions and free space for reuse). <br /><br />So my transaction log is full! And there's no space on the <br />disk left to start a back up. Thankfully this is in our <br />dev environment so I issue a:<br /><br />BACKUP LOG PoeticSQL_BlogEntries WITH truncate_only<br /><br />There is another option, considering that this is <br />a development environment: <br />Temporarily change the recovery model to SIMPLE, <br />issue the syntax CHECKPOINT in a query window <br />and then change the recovery model back to full (if needed). <br />(Some companies make it a policy to back up their dev <br />environments)<br /><br /><strong>Avoiding trouble in the Future</strong><br />If one were so inclined, one might attempt to glean insight <br />into the environment and log usage by tracking the result of <br />DBCC SQLPERF(logspace) over time and then analyzing it. This <br />is accomplished by sending the output of this command to a <br />table and scheduling it's execution via a SQL Agent job.<br /><br />You'll need a few things:<br /><br />CREATE PROC dbo.spTLogSizeTracker <br />AS <br />DBCC SQLPERF(logspace) <br />GO <br /> <br /><br />CREATE TABLE dbo.LogTLOGSize <br />( <br />id INT IDENTITY (1,1), <br />logDate datetime DEFAULT GETDATE(), <br />DB sysname, <br />logSize decimal(18,5), <br />logUsed decimal(18,5) <br />) <br />GO <br /> <br /><br /><br />CREATE PROC dbo.spGetTLOGSizes <br />AS <br />SET NOCOUNT ON <br /><br />CREATE TABLE #temp<br />( <br />DB sysname, <br />logSize decimal(18,5), <br />logUsed decimal(18,5), <br />status INT <br />) <br /><br />INSERT INTO #temp<br /> EXEC spTLogSizeTracker <br /><br />INSERT INTO LogTLOGSize (DB, logSize, logUsed) <br />SELECT DB, logSize, logUsed <br />FROM #temp<br /><br />DROP TABLE #temp<br />GO<br />With the above supporting structures in place you can create <br />a SQL job in Management Studio to run the spTLogSizeTracker <br />procedure on a regular schedule such as hourly. <br /><br />Then simply query the table and voila! You have a wealth <br />of information about your database's relative workload as seen <br />through the transaction log.<br /><br />Happy TSQLing<br />SQLPoet<br /><br /><br /><br />*For example (filegrowth = 1MB) means the transaction log will grow by 1 meg every time the log needs to expand.SQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com1tag:blogger.com,1999:blog-8353606891916759202.post-29806527885715785292008-01-15T09:33:00.000-05:002008-01-17T10:20:43.827-05:00Arithmetic overflow error converting expression to data type intToday I was troubleshooting a query that was not returning expected records. In so doing, I decided to get a count of the records SQL Server 2005 was returning. Here is the query I used:<br /><br />SELECT count(*)<br />FROM jdr WITH (NOLOCK)<br />INNER JOIN HR WITH (NOLOCK)<br /> on hr.IB_ID = jdr.ib_id <br /><br />Upon execution, I got the following SQL Server error:<br /><br /><em>Arithmetic overflow error converting expression to data type int.</em><br /><br />As it turns out, the resulting count(*) was in fact out of range for an int data type. <br />See this <a href="http://msdn2.microsoft.com/en-us/library/ms163363.aspx">SQL Books online article </a>.<br /><br />Using COUNT_BIG(*) instead of COUNT(*), I avoided the error:<br /><br />SELECT COUNT_BIG(*)<br />FROM jdr WITH (NOLOCK)<br />INNER JOIN HR WITH (NOLOCK)<br /> on hr.IB_ID = jdr.ib_id <br /><br />--333,514,544,659<br /><br />So the moral of the story is that if you are counting more than ~2 million records, better to use COUNT_BIG(*)<br /><br /><br />Happy TSQLing<br />SQLPoetSQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com3tag:blogger.com,1999:blog-8353606891916759202.post-10707647192996163722008-01-10T16:37:00.000-05:002008-01-10T16:41:52.179-05:00Unused Index--Use this syntax to determine which indexes are not used in SQL Server 2005<br /><br />SELECT object_name(stats.object_id), <br /> CASE i.[index_id] <br /> WHEN 0 THEN N'HEAP' <br /> ELSE i.[name] <br /> END AS [Index Name], * <br />FROM sys.dm_db_index_usage_stats stats <br /> JOIN sys.objects o on stats.[object_id] = o.[object_id]<br /> JOIN sys.indexes i on o.[object_id] = i.[object_id]<br />WHERE [database_id] = DB_ID() <br /> and user_scans = 0 <br /> and user_seeks = 0 <br /> and user_lookups = 0<br /> and i.name not like 'pk%'SQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com0tag:blogger.com,1999:blog-8353606891916759202.post-72923251772670153462007-11-29T09:03:00.000-05:002007-11-30T17:15:56.702-05:00Dependencies/*<br />Ever tried to use the <span class="blsp-spelling-error" id="SPELLING_ERROR_0">sysdepends</span> table to determine dependencies among your database objects? It doesn't ALWAYS work. There are those occasions when during creation, you might get a warning message from <span class="blsp-spelling-error" id="SPELLING_ERROR_1">SQL</span> Server that says that a procedure was created but that the row was not added to <span class="blsp-spelling-error" id="SPELLING_ERROR_2">sysdepends</span>:<br /><br /><br /><span style="font-family:arial;"><em>Cannot add rows to <span class="blsp-spelling-error" id="SPELLING_ERROR_3">sysdepends</span> for the current stored procedure because it<br />depends on the missing object '<span class="blsp-spelling-error" id="SPELLING_ERROR_4">spGetSQLPoetsStats</span>. The stored procedure<br />will still be created.</em></span><br /><br /><br />I think we have all had this issue and know why it happens. The purpose of this post is to show you another way to 'skin-the-cat'.<br /><br /><br />Take for instance this undocumented procedure: <span class="blsp-spelling-error" id="SPELLING_ERROR_5">sp</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_6">MSdependencies</span>. If you Googled it you might encounter some difficulty in finding just how to use it. It is one of many undocumented tools that Microsoft 'provides' with <span class="blsp-spelling-error" id="SPELLING_ERROR_7">SQL</span> Server. It should be noted at this point that Microsoft does not guarantee future support for undocumented objects. It is recommended that you do not use this in your application code but simply as a means of <span class="blsp-spelling-error" id="SPELLING_ERROR_8">adhoc</span> reporting for as long as we can.<br /><br /><br />The task in this case was to provide the <span class="blsp-spelling-error" id="SPELLING_ERROR_9">QA</span> folks with a list of the objects they would be testing and the dependencies thereof. As stated earlier, <span class="blsp-spelling-error" id="SPELLING_ERROR_10">sysdepends</span> does not ALWAYS help and this was one of those times.<br /><br /><br />If you EXECUTE <span class="blsp-spelling-error" id="SPELLING_ERROR_11">sp</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_12">msdependencies</span> '?' in the query editor against <span class="blsp-spelling-error" id="SPELLING_ERROR_13">sql</span> server's master database you will find a list of parameter values you can specify. Pay particular attention to the flag parameter. Therein lies the magic:)<br /><br />I am using 266751 which shows the objects on which these depend and also 4607 to show the objects that depend on those objects...confused? The result set should fill in the holes in your understanding.<br /><br />*/<br /><br />set <span class="blsp-spelling-error" id="SPELLING_ERROR_14">nocount</span> on<br /><br />declare @<span class="blsp-spelling-error" id="SPELLING_ERROR_15">tablename</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_16">varchar</span>(100)<br />declare @type <span class="blsp-spelling-error" id="SPELLING_ERROR_17">varchar</span>(100)<br />declare @<span class="blsp-spelling-error" id="SPELLING_ERROR_18">ServerName</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_19">varchar</span>(100)<br /><br />set @<span class="blsp-spelling-error" id="SPELLING_ERROR_20">ServerName</span> = db_name()<br /><br />Declare @temp2 table<br />(<span class="blsp-spelling-error" id="SPELLING_ERROR_21">ServerName</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_22">varchar</span>(100),<br /><span class="blsp-spelling-error" id="SPELLING_ERROR_23">ObjectName</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_24">varchar</span>(100),<br /><span class="blsp-spelling-error" id="SPELLING_ERROR_25">objecttype</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_26">varchar</span>(100),<br />Dependency <span class="blsp-spelling-error" id="SPELLING_ERROR_27">varchar</span>(100),<br /><span class="blsp-spelling-error" id="SPELLING_ERROR_28">ObjectName</span>_2 <span class="blsp-spelling-error" id="SPELLING_ERROR_29">varchar</span>(100),<br /><span class="blsp-spelling-error" id="SPELLING_ERROR_30">Objecttype</span>_2 <span class="blsp-spelling-error" id="SPELLING_ERROR_31">varchar</span>(100))<br /><br />Declare @temp table<br />(<span class="blsp-spelling-error" id="SPELLING_ERROR_32">otype</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_33">varchar</span>(100),<br /><span class="blsp-spelling-error" id="SPELLING_ERROR_34">oObjname</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_35">varchar</span>(100),<br /><span class="blsp-spelling-error" id="SPELLING_ERROR_36">oOwner</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_37">varchar</span>(50),<br /><span class="blsp-spelling-error" id="SPELLING_ERROR_38">oSequence</span> int)<br /><br />Select top 1 @tablename = name, @Type = type_<span class="blsp-spelling-error" id="SPELLING_ERROR_39">desc</span> from <span class="blsp-spelling-error" id="SPELLING_ERROR_40">sys</span>.objects<br />where type in ('<span class="blsp-spelling-error" id="SPELLING_ERROR_41">FN</span>','P','<span class="blsp-spelling-error" id="SPELLING_ERROR_42">TF</span>','V','U')<br />and is_ms_shipped <> 1<br />order by name<br /><br />while @<span class="blsp-spelling-error" id="SPELLING_ERROR_43">tablename</span> is not null<br />Begin<br /><br />Insert into @temp<br />EXEC <span class="blsp-spelling-error" id="SPELLING_ERROR_44">sp</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_45">MSdependencies</span> @<span class="blsp-spelling-error" id="SPELLING_ERROR_46">tablename</span>, null, 266751 --this flag shows which objects depend on these<br /><br />Insert into @temp2<br />select @<span class="blsp-spelling-error" id="SPELLING_ERROR_47">ServerName</span>, @<span class="blsp-spelling-error" id="SPELLING_ERROR_48">tablename</span>, @type, 'Is Used by', <span class="blsp-spelling-error" id="SPELLING_ERROR_49">oObjname</span>,<br />Case <span class="blsp-spelling-error" id="SPELLING_ERROR_50">otype</span><br />When 4 then 'View'<br />When 16 then 'Stored Procedure'<br />When 256 then 'Trigger'<br />When 8 then 'Table'<br />When 1 then 'Function'<br />Else <span class="blsp-spelling-error" id="SPELLING_ERROR_51">otype</span><br />end<br />from @temp<br /><br />Delete from @temp<br /><br />Insert into @temp<br />EXEC <span class="blsp-spelling-error" id="SPELLING_ERROR_52">sp</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_53">MSdependencies</span> @<span class="blsp-spelling-error" id="SPELLING_ERROR_54">tablename</span>, null, 4607 --this flag shows the objects these depend on<br /><br />Insert into @temp2<br />select @<span class="blsp-spelling-error" id="SPELLING_ERROR_55">ServerName</span>, @<span class="blsp-spelling-error" id="SPELLING_ERROR_56">tablename</span>, @type, 'Is Dependent on', <span class="blsp-spelling-error" id="SPELLING_ERROR_57">oObjname</span>,<br />Case <span class="blsp-spelling-error" id="SPELLING_ERROR_58">otype</span><br />When 4 then 'View'<br />When 16 then 'Stored Procedure'<br />When 256 then 'Trigger'<br />When 8 then 'Table'<br />When 1 then 'Function'<br />Else <span class="blsp-spelling-error" id="SPELLING_ERROR_59">otype</span><br />end<br />from @temp<br /><br />Delete from @temp<br />Print @<span class="blsp-spelling-error" id="SPELLING_ERROR_60">tablename</span><br /><br />set @type = (Select top 1 type_<span class="blsp-spelling-error" id="SPELLING_ERROR_61">desc</span> from <span class="blsp-spelling-error" id="SPELLING_ERROR_62">sys</span>.objects<br />where type in ('<span class="blsp-spelling-error" id="SPELLING_ERROR_63">FN</span>','P','<span class="blsp-spelling-error" id="SPELLING_ERROR_64">TF</span>','V','U')<br />and is_ms_shipped <> 1<br />and name > @<span class="blsp-spelling-error" id="SPELLING_ERROR_65">tablename</span><br />order by name)<br /><br />set @<span class="blsp-spelling-error" id="SPELLING_ERROR_66">tablename</span> = (Select top 1 name from <span class="blsp-spelling-error" id="SPELLING_ERROR_67">sys</span>.objects<br />where type in ('<span class="blsp-spelling-error" id="SPELLING_ERROR_68">FN</span>','P','<span class="blsp-spelling-error" id="SPELLING_ERROR_69">TF</span>','V','U')<br />and is_ms_shipped <> 1<br />and name > @<span class="blsp-spelling-error" id="SPELLING_ERROR_70">tablename</span><br />order by name)<br /><br />end<br /><br />select * from @temp2 order by <span class="blsp-spelling-error" id="SPELLING_ERROR_71">objecttype</span><br /><br />/*<br />With the results of this script, we can provide the <span class="blsp-spelling-error" id="SPELLING_ERROR_72">QA</span> folks the list they need to document, test, etc.<br /><br />Happy <span class="blsp-spelling-error" id="SPELLING_ERROR_73">TSQLing</span><br /><span class="blsp-spelling-error" id="SPELLING_ERROR_74">SQLPoet</span><br />*/SQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com2tag:blogger.com,1999:blog-8353606891916759202.post-34145486133891930642007-11-14T09:18:00.000-05:002007-11-14T10:02:10.067-05:00Using SQL to write SQL/*<br />You may be asked to create users for your company's inhouse application. We're not talking SQL Server Logins and Database Users, no. We're talking security credentials that most web applications administer through tables seemingly common to most businesses like the [Users] table.<br /><br />If you were to get hit with one of these requests, your first reaction might be to ask whether a process or scripts for this sort of thing already exist. Depending on the size and age of your company and the quality of your predecessor DBAs, there might not be any processes or scripts available and so you must create one. Sure you COULD go to the GUI and then script your changes out but you still have to manually enter the data values for the new record. Plus using the GUI is a much less efficient way to get these users into every environment in which they are needed (thanks to the human element and our tendency to error).<br /><br />I recently got one of these requests in which the author of the ticket specified three new users to be created and modelled after another user (permission-wise). Our application keeps user information in a series of tables with the prefix User. In order for a user to have access to the application, they will need a record in the [User] table.<br /><br />No brainer, so far?<br /><br />To INSERT into the User table we might use something like:<br />*/<br /><br />INSERT [User](User_Group_ID,User_Name,Password_Hash,Full_Name,Email_Address,[...])<br />VALUES ('3121', 'sqlpoet', 'B44DDA1DADD351948FCACE1856ED97366E679239',[...])<br /><br />/*<br />Even with a manually written insert statement like this one we still have to get the data of the user we are required to model these new ones after. Since I did some preliminary querying on the the table, I know that our model user's User_ID = 11106<br />*/<br /><div align="left"><br />SELECT * FROM [User] WHERE User_ID = 11106</div><br />/*<br />Using the data resulting from the query above, we plug in the common data values changing variable datavalues such as User_Name and then copy and paste each value into its cooresponding place in the INSERT statement.<br /><br />But what if we re-wrote our SELECT to write the INSERT for us?<br />*/<br /><br /><div align="left">SELECT </div><div align="left">'INSERT [User](User_Group_ID,User_Name,Password_Hash,Full_Name,Email_Address,[...])</div><div align="left">VALUES(''' + convert(varchar(20),User_Group_ID) + ''', ''' + [User_Name] + ''', ''' + char(10) +<br />Password_Hash + ''', ''' + Full_Name + ''', ''' + Email_Address + ''', ''' + [...] + ''')'</div><div align="left">FROM [User] WHERE User_ID = 11106 --our model user</div><br />/*<br />The result of the above query, done correctly, will yield a statement that with a little editing can be copied and pasted into your change control script so that these users easily move from environment to environment whenever executed. Or you can execute them to create these users now...its up to you. Happy TSQLing.<br /><br />SQL Poet<br />*/SQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com0tag:blogger.com,1999:blog-8353606891916759202.post-33037211933615750202007-10-10T15:29:00.000-04:002007-11-14T11:33:59.727-05:00Create permissions statements Procs and/or UDFs/**********************************<br />** Today's post concerns security. I am in a smaller company that<br />** is just getting used to having DBAs. As such, the general care<br />** and feeding of the database and servers was similar to the<br />** marshal law of the Wild West. All developers had sa permissions<br />** to the database servers! Which in essence means everyone had a<br />** gun and could accidentally (or otherwise) shoot the database<br />** in the foot at will. This included the user that was created for the<br />** application as well. We want to switch to Role based permissions<br />** for the application.<br />**<br />** Long story short, after sufficient warning, we removed the elevated<br />** permissions that the developers were enjoying. We gave the application's<br />** user account db_datareader (not the best option but I digress) and then<br />** scripted all the SELECT and EXECUTE permissions pertaining to<br />** Procedures and UDFs for the application's user account. We did this<br />** last scripting step using a procedure included in the install of SQL Server:<br />** sp_helprotect; which brings me to this small disclaimer:<br />**<br />** <strong><em>This works only in SQL Server 2000 this is because the integral </em></strong><br /><strong><em>** piece of this solution (sp_helprotect) works differently in 2005:(</em></strong><br />**<br />** This script creates the Role and adds the specified user to it.<br />** Then it creates permissions statements for each Proc<br />** or UDF based on the permissions of the specified user. You must then<br />** copy/paste the results into Query Analyzer or the Management Studio<br />** editor.<br />**<br />** For our example, let us assume that the application database user<br />** account is named 'sqlpoet'. This account already has permissions directly<br />** granted to it for executing procedures and UDFs. We want to transfer those<br />** permissions to our new database role.<br />*****************************/<br />SET NOCOUNT ON<br /><br />IF NOT EXISTS (SELECT *<br />FROM dbo.sysusers<br />WHERE [Name] = N'ExecuteProcAndUDFs' and uid > 16399)<br />EXEC sp_addrole N'ExecuteProcAndUDFs'<br />GO<br />DECLARE @UserAcct varchar(200)<br /><br />/*SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!! */<br />SET @UserAcct = 'sqlpoet'<br />/*SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!! */<br /><br />EXEC sp_addrolemember N'ExecuteProcAndUDFs', @UserAcct<br />GO<br />IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE [Name] like '%temp1%')<br />DROP TABLE #temp1<br />go<br />CREATE TABLE #temp1 ( --table to hold the current permissions set<br />Owner varchar(50),<br />Object varchar(200),<br />Grantee varchar(200),<br />Grantor varchar(200),<br />ProtectType varchar(200),<br />Action varchar(200),<br />[Column] varchar(200))<br />GO<br />INSERT #temp1 --get the data<br />EXEC sp_helprotect<br />go<br />SELECT DISTINCT ProtectType --create the GRANT statements<br />+ ''<br />+ [Action]<br />+ ' on '<br />+ object<br />+ ' to ExecuteProcAndUDFs' /* optionally you can use Grantee field but I needed to grant these permissions to the hard-coded database role you see here.*/<br />FROM #temp1<br />JOIN information_schema.routines<br />ON object = specific_name<br />--cleanup<br />DROP TABLE #temp1<br />/*Rememeber to copy the results and execute them in the appropriate database<br />Happy TSQLing<br />*/SQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com0tag:blogger.com,1999:blog-8353606891916759202.post-38938484251167575022007-09-24T12:04:00.000-04:002007-09-25T16:51:13.296-04:00Who changed my stored procedure?/**********************************<br />** I currently work in an environment<br />** in which developers are<br />** allowed to make changes<br />** directly to the database objects<br />** in SQL Server.<br />**<br />** I have always endorsed empowerment<br />** and as a database developer/DBA for the<br />** same company, I do not feel threatened<br />** by their continued development in an<br />** environment under which they have this<br />** level of control.<br />**<br />** The problem arrises when we (as the database<br />** professionals on the development side of the house)<br />** are required to keep track of the data model and<br />** all objects. In order to accomplish this feat, we must be<br />** explicitly aware of all changes that occur. Well...<br />** <br />** ...here's a really cool tool that records these changes<br />** by taking advantage of features in SQL Server 2005<br />** namely the databse level trigger and the EVENTDATA()<br />** xml data type.<br />** <br />** What follows is a script that will allow you to see<br />** who is making changes to your objects in SQL<br />** Server 2005 databases. <br /><br />** To see a full explanation of this solution<br />** by the author: <a href="http://www.sqlteam.com/author/chris-rock">Chris Rock</a> (follow that link!!!)<br />**<br />** Happy TSQLing<br />** SQLPoet<br />************************************************/<br /><br />USE [master]<br />GO<br />CREATE DATABASE [EventsLogDB]<br />GO<br />USE [EventsLogDB]<br />GO<br />--This goes into the eventslogdb database<br />CREATE TABLE [dbo].[ChangeLog](<br />[LogId] [int] IDENTITY(1,1) NOT NULL,<br />[DatabaseName] [varchar] (256) ,<br />[EventType] [varchar](50) ,<br />[ObjectName] [varchar](256) ,<br />[ObjectType] [varchar](25) ,<br />[SqlCommand] [varchar](max) ,<br />[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),<br />[LoginName] [varchar](256) NOT NULL<br />) ON [PRIMARY]<br /><br />--Note : Our DDL trigger goes into the database to monitor and is defined as:<br />USE [YourMonitoredDatabase]<br />GO<br />CREATE TRIGGER backup_objects<br />ON DATABASE<br />FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,<br />CREATE_TABLE, ALTER_TABLE, DROP_TABLE,<br />CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION<br />AS<br />SET NOCOUNT ON<br />DECLARE @data xml<br />SET @data = EVENTDATA()<br />INSERT INTO eventslogdb.dbo.changelog(databasename, eventtype,<br />objectname, objecttype, sqlcommand, loginname)<br />VALUES(<br />@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),<br />@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),<br />@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),<br />@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),<br />@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),<br />@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')<br />)<br />GOSQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com0tag:blogger.com,1999:blog-8353606891916759202.post-56783817679114090572007-09-21T12:51:00.000-04:002007-09-21T13:01:08.679-04:00read the name of the sql server agent accountAs time goes on and the push to make things more dynamic continues, I find myself needing to create procedures and DTS/SSIS packs more intelligent about the environment to which it is being deployed. This is especially true now with the upgrade from earlier versions of SQL Server to SQL 2005 and soon SQL 2008.<br /><br />This little script is what I use to determine the name of the sql server agent account so that I can correctly set the @owner variable of the sp_add_job procedure.<br /><br />One could concievably put this into a stored procedure, if one so desired:)<br /><br />Enjoy:<br /><br /><br />-- script to read the name of the sql server agent account<br />/**********************************************************************<br />-- Declare your variables...<br />***********************************************************************/<br />DECLARE @rc int,<br />@machine nvarchar(128),<br />@instance nvarchar(128),<br />@profile nvarchar(128),<br />@regpath nvarchar(256),<br />@account nvarchar(128)<br /><br />IF (charindex(N'7.00', @@version, 0) > 0)<br />BEGIN<br />SELECT @instance = NULL<br />END<br />IF (charindex(N'8.00', @@version, 0) > 0) or (charindex(N'9.00', @@version, 0) > 0)<br />BEGIN<br />SELECT @machine = CONVERT(nvarchar(128), serverproperty('machine')),<br />@instance = CONVERT(nvarchar(128),serverproperty('instance'))<br />END<br />/**********************************************************************<br />-- Check if not running SQL Server as LocalSystem<br />-- @rc 0 = LocalSystem, 1 = running under account<br />***********************************************************************/<br />EXEC @rc = master.dbo.xp_MSLocalSystem @machine, @instance<br />IF (@rc = 0)<br />BEGIN<br />PRINT 'SQL Server is running under the LocalSystem account'<br />RETURN<br />END<br />/**********************************************************************<br />-- Get account name running SQL Server<br />**********************************************************************/<br />IF (@instance IS NULL)<br />BEGIN<br />SELECT @regpath = N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'<br />END<br />ELSE<br />BEGIN<br />SELECT @regpath = N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT$' + @instance<br />END<br />EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',<br />@regpath,<br />N'ObjectName',<br />@account OUTPUT,<br />N'no_output'<br />PRINT 'SQL Server is running under account ' + @account<br />RETURN<br />GOSQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com0tag:blogger.com,1999:blog-8353606891916759202.post-59333798173729070352007-09-21T12:04:00.000-04:002007-09-21T12:07:48.877-04:00In the beginning...So today I started my first ever blog as an online repository of all of the SQL scripts I keep in a folder called Code Library on my flash drive. This venue strives to make my scripts available to me regardless of whether I remebered my keys or not. As long as I have a connection to the internet, I can get to these little nuggets of time saving splendor.<br /><br />In time I hope that this can be a place for more folks to share and store their favorite scripts:)<br /><br />Happy TSQLing!SQLPoethttp://www.blogger.com/profile/11355501692859512735noreply@blogger.com0