Update Orphaned SQL Users

SQL Script to relink orphaned users to logins

It's quite a common task to have to move one or more databases from one server to another and this usually causes issues with orphaned users that have no related SQL login due to the SID values mismatching. The following is taken from an MS Knowledge base article about the problem:

When you move a database from one server that is running SQL Server to another server that is running SQL Server, a mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide the sp_change_users_login system stored procedure to map these mismatched users. However, you can only use the sp_change_users_login stored procedure to map standard SQL Server logins and you must perform these mapping for one user at a time.

Therefore the issue becomes how to sync up the logins in the copied database that relate to Login SID's that no longer exist on the server. If you try to manually re-save the login and grant it access to the copied database you are likely to run into problems with an error that prevents you from accomplishing the task such as:

User or role already exists in the current database

Therefore you either have to run a script to delete the users and then re-create them all correctly or do as I did and use some SQL to output the necessary TSQL statements that use the system views sys.sql_logins and sysusers and the stored procedure sp_change_users_login to relink the orphaned user to an SQL login.

-- example output to relink logins to users USE myDB1 GO EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase101', 'mydatabase101' GO USE myDB2 GO EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase_admin_101', 'mydatabase_admin_101' GO

The script has two options which allows you to either just output a list of all the orphaned users and their related databases so that you can run any reconfigure statements yourself. The other option will output all the necessary TSQL statements to relink the user to an SQL login of the same name.

IF @MODE = 'SQL'
BEGIN
 -- loop each orphan and generate auto fix code

 SELECT @MaxRow = COUNT(*), @RowID = 0
 FROM #RESULTS

 IF @MaxRow > 0
   BEGIN
  WHILE @RowID > @MaxRow
    BEGIN
   SELECT @RowID = @RowID + 1

   SELECT @DatabaseName = DatabaseName
    ,@OrphanedUser = OrphanedUser
   FROM #RESULTS
   WHERE RowID = @RowID

   -- output the TSQL to relink the user with the login

   SELECT @SQL =  'USE '   + @DatabaseName + + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)
      +'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + @OrphanedUser + ''', ''' + @OrphanedUser + '''' + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)

   PRINT @SQL
    END
   END

END
ELSE -- output the orphans
BEGIN
 SELECT *
 FROM #Results
 ORDER BY [DatabaseName], [OrphanedUser]
END

Obviously this is no good if you use different names for your logins and their corresponding users but as I don't it was perfect for me and enabled me to quickly resolve all the issues I had today when moving some large and important databases to a new server.

The code specifically ignores certain system users as well as all Windows Logins. It is specifically for SQL 2005 and above and is based on an SQL 2K script I found at the following site:  vyaskn.tripod.com. 

If you require a script for an SQL 2K or SQL 7 server then I suggest checking out that site as this script will not work due to changes in the SQL System Views it uses.

To get the full source code please visit the following link:

Download the SQL

By Srictly-Software

Creating Twitter Mashups

Using Free Online Tools to Create Twitter Mashups

If you are into Twitter then you may have wanted at some time to automate some tweets. There are many reasons you may want to do this such as creating new content quickly or providing an online presence when your not physically around. You may want to follow certain hash tags and retweet the same post with new hash tags or you may want to auto retweet relevant messages from certain friends or lists. Whatever reason you require this feature for you are not alone and there are already many tools out there that you can purchase to provide this functionality.

If however you don't want to spend any money and want to learn the necessary skills to do the job yourself then there are also many ways to accomplish this quickly. I managed to create an automated tweetbot within an hour tonight that follows a series of search terms as well as retweeting certain friends messages. It was very easy to do and I didn't have to write one line of code to get it working.

The aim of the Tweetbot was simple. I wanted content for a new Twitter account and I wanted to drive users to a site without having to manually write lots of Twitter updates. The updates I do write I wanted to retweet with new hash tags as well as retweeting certain relevant posts from friends.

By creating this Tweetbot I am delivering my earlier posts to a new audience who maybe following different hash tags as well as driving new users to my site by retweeting other related tweets automatically. I will see how this method works in terms of generating traffic over time but from a development point of view it was quite simple to set up.

1. Create a Yahoo Account if you haven't already got one.

2. Create a Twitter Account if you haven't already got one.

3. Find your source data which in my case was a mixture of Twitter RSS feeds. If you want to access a feed relevant to search terms then you can use the search.twitter.com interface. For example the following RSS is for the search term Javascript:

4. To combine the input feeds, analyse the content and reformat them into one output feed we are going to a great on-line tool called Yahoo Pipes. This free tool allows you to do lots of cool operations on various forms of input data with a drag n drop interface. You do this by creating a visual flow diagram with multiple steps going from your input source which can be one or more feeds through various filters and then to the pipe output. Each filter can be set to look for terms within the posts e.g you may want to only include posts that mention the word "javascript" or "SQL" in the title or the content. Or you may want to only include the post if it doesn't mention certain words or terms. You can do quite complex operations including using regular expressions to reformat the content, sort operations and clean-ups to remove duplicate content. The following article explains it quite well:

http://ryanpricemedia.com/2009/02/03/howto-yahoo-pipes-auto-retweet-recipe-for-twitter/

4. Once your pipe is saved you will be able to access it in RSS format from the supplied URL.

5. To get this data into your Twitter account you can either use the Twitter API and write your own code to parse the feed on a timed interval and add the posts to your twitter account e.g by using a CRON job that runs a Perl, PHP, VBScript:

http://apiwiki.twitter.com/Open+source

6. Or you could do what I did which is use one of the free Twitter feed applications that are available on the web such as twitterfeed.com. They obviously do something similar to point 5 in that you specify the RSS feed you want to import (e.g your Yahoo Pipe RSS feed) and set some configuration options such as the Twitter account details, number of items to parse, extra content to add to the posts etc.

The only downside with using twitterfeed.com is that the job only runs every half an hour which may not be a small enough interval for you. They obviously have a CRON job that runs every 20 minutes or so and parses all the feeds setup through the application so its not surprising that they cannot offer a smaller timescale however this does prevent you from over tweeting.

There are other apps out there and it would be quite simple to create your own Twitter app if you so wished with very little script, a timed job and the relevant input files.

I managed to create my PIPE within an hour and it had some quite complex content parsing and reformatting functionality so I was surprised at how easily I could do what I wanted without resorting to writing my own code. Try it yourself.

Grant Execute Permission to all Stored Procedures and Functions

How to grant execute permission to all your stored procedures and functions

Another useful stored procedure I have in my toolkit that I find myself using over and over on any system that I work on is the following proc that allows me to grant execute permission to all stored procedures and user defined functions within a database to a particular logon.

Sometimes I find myself creating multiple stored procedures or functions in one go, either through a CRUD proc generator script or just manually but without the necessary execute permissions added to each procedure or function they will fail to execute. Therefore its very handy if you have forgotten to add the GRANT EXECUTE statements as you went along to do it all in one batch.

The procedure makes use of the sysobjects system view to return a list of all the stored procedures and user defined functions within the database the procedure is called from. I then loop through each one and generate the necessary SQL to apply EXECUTE permission to that proc or UDF to the supplied logon.

I have also added a flag which allows you to print all the GRANT EXECUTE statements without running them so that you can check the output before running it.

The code is below.

CREATE PROCEDURE [dbo].[usp_sql_grant_execute_permission] 

 @login VARCHAR(50) = 'mylogon',
 @RunEXEC BIT = 1 --1 will run the statements, 0 = Prints them

AS

SET NOCOUNT ON

DECLARE @procname VARCHAR(50),
  @Rows INT,
  @Count INT

-- Populate the temporary table with user defined stored procs and functions
SELECT [name] INTO #tmp 
FROM sysobjects 
WHERE (xtype = 'P') OR  (xtype = 'FN' OR xtype = 'TF')

SELECT @Rows = @@ROWCOUNT, @Count = 0


-- Iterate through each of the rows on the table
WHILE (@Count < @Rows)
BEGIN
 -- get next item
 SELECT TOP 1 @procname = [name] FROM #tmp

 -- Prepare and execute SQL statement
 DECLARE @sql VARCHAR(255)
 SET @sql = 'GRANT EXEC ON ' + @procname + ' TO ' + @login
 
 -- do we run or print them out
 IF @RunEXEC = 1
   EXEC (@sql)
 ELSE
   PRINT @SQL
 
 -- delete record from temp table 
 DELETE #tmp WHERE [name] = @procname
 
 SELECT @Count = @Count + 1
END

You call it simply like so:

EXEC usp_sql_grant_execute_permission 'mylogin'

It's one of those simple procedures that are very handy to have and save a lot of time. Its very quick to run so if I ever come across any permission denied errors on my development system I just run this procedure first to ensure all my procedures and functions have the necessary execute permissions to run correctly.