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!

Advertisements

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