Finding All The Logins Not Associated to Database Users

Today I ran into a particular issue, consolidating user accounts. In this particular exercise, I needed to remove old user accounts that were not tied to any databases. Here is a neat trick I did to get a reasonable number of logins to look at for disabling.

CREATE TABLE ##names (NAME SYSNAME);
EXEC sp_msforeachdb ‘insert into ##names (name) select name from [?].sys.sysusers’;

SELECT name,
loginname
FROM sys.syslogins l
WHERE NOT EXISTS ( SELECT *
FROM ##names u
WHERE u.name=l.name )

Using the unsupported sp_msforeachdb stored procedure I could dump all the usernames into a table and compare it to the logins. Simple, quick, dirty…But saved a lot of time!

Advertisement

SQL Server Delete From vs. Truncate Table

There are some very important distinctions between these that I feel I need to explain.

Delete From
First and foremost delete is used for deleting a limited number of rows from a table. This is not to be used for deleting all rows in a table.

Another important note is that this is a logged process meaning it writes this delete into the transaction log. Which is good if you ever need to recover those rows.

Also deleting rows does not reset or reseed, more technically, any identity columns. So deleting the last row will not decrement the identity column.

Truncate Table
So this function is to delete all rows in the table.

Unlike Delete, this operation is very efficient, mainly because it is not a logged process. Use this for very specific reasons, testing, repeating processes, etc.

Also it will reseed the identity column in your table. So for example, it will delete all the rows in your table and the identity will be reset to its initial value, by default this is the number 1.

SQL Server Reporting Services ~ MultiValued Parameters

This is a really good article on multivalued parameters in a stored procedure. Very good read for SQL Server Reporting Services.

Tech Updates

In SSRS reports we can have multi valued paremeters, which means we can select multiple values for that parameter and based on all those selected values result in the report is shown. We can write the SQL query to pass those parameter’s values and populate the data set, but sometimes it is required or to optimize the performance we need to use the stored procedure instead of direct sql query. This stored procedure will be taking the report paremeter as input and returing the result set to populate the data set.

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values…

View original post 398 more words

Troubleshooting MSDTC: The transaction manager has disabled its support for remote/network transactions

This guy has a great article that I have re-blog: http://wp.me/p1qkE8-2I

MS Access Error: Too few parameters. Expected x

If you are getting this error it means you are probably working. Here is the deal: this error happens when you try to access an Access Database through ODBC or OLEDB. Chances are you are trying to use Access’s standard of using double quotes (“), replace them with single quotes(‘) and you should be off and running.

If that still didn’t fix your problem the error is probably quite literal in that you forgot to add a parameter to your query or that you are missing some search criteria.

Let me know if this doesn’t work for you and I can help you get up and running.

Altering a Table – “Saving Changes is Not Permitted”

So you may or may not know this. SQL Server 2008 and up have a restrictive setting for altering tables (to prevent users from shooting themselves in the foot). To get around this message, go to Tools>Options>Designers; then un-check “Prevent saving changes that require table re-creation”.

That is well and good but has lead me to ask the question, what changes will require table re-creation? Here is what I found:

• Changing column sizes.
• Setting a column to allow nulls or to not allow nulls.
• Changing a column type to a type that does not convert well. Int to bigint is fine but nvarchar to text requires table re-creation.
• Changing foreign key constraints.
• Modifying identity properties of columns.

Here is something that you can do though:

• Add  a column and it’s properties.
• Delete a column, kind of strange since this would be destroying data. What gives Microsoft?

SQL Server News – Denali!!!

If you are interested in taking a look at the latest and greatest…Denali is found here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

What’s new?

Column based query acceleration is greatly improved. Computed values and aggregates are calculated much, much faster (at least by a factor of 10 according to Microsoft).

Improved performance for FileStream. FileStream was a technology that was introduced in SQL Server 2008.

BI has also been greatly improved with a lot more functionality.

Better programming interfaces.

A workflow approach has been included for better integration.

It now uses Visual Studio 2010 for its interface!

There is now an actual dashboard that is more responsive, a nice bell or whistle (whichever you prefer).

The official Microsoft page for all future versions of SQL Server is here:

http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

SQL – Turning Several Result Sets into a Single Result Set

In case you need to get several result sets through a single result set, for whatever reason. In my case I have program that works on one result set at a time that needed some aggregate data displayed at the end of the report. Here is what you do:

Create a temporary table to store the table with the most fields in your result set. Everything will use this table for its data. Be sure create a primary key using an int identity(1,1). Without creating a primary key the data will not be stored in any order.

Order the data the way you need it to display and insert it into the temporary table. Then add following result sets to this table.

Note: Data types are important in getting this to work and character/string based data types tend to work the best.

Computers

Creating Fixed Width / Ragged Right Files Through SQL

Here is a great tip I thought of on-the-fly. Say you need to make a fixed width file for something that needs to get out right away. Here is what you do: convert to char!

Concatenate all the fields together and converting all of the data to the char data type will allow you to save the results in a perfect fixed width / ragged right format.

Simple and fast!

SQL Server Maintenance Plans vs. Powershell Scripts

I have been working on maintenance plans for some time now and while they are very powerful I am starting to wonder if Powershell might be a better platform to perform my server maintenance. I am starting to lean towards Powershell and here is why. Keep in mind that this is up for debate and I am sure this could stir up some controversy. 

With Powershell it is easier to get access to the OS.  With maintenance plans it gets a little more complicated. Maintenance plans are built for SQL DBAs, not for the person who owns the whole box. Sometimes DBAs have limited access to the server, so for those people Powershell isn’t the best choice. Also if you don’t have a programming background, Powershell might not be for you although you might want to consider it since there are some very simple scripts that can get some serious information to you very quickly, but I digress.

Scripts are highly portable. SSIS packages are as well but you can’t edit them easily without BIDS. Notepad is all you need for Powershell.

I am going to keep you all posted as I am making changes to my scripts.  Once I make a really good script I will post it.