Archive for the 'Database' Category

SQL Database moved to another server – how to fix orphaned logins

Last night I was on support for a software deployment that saw one of our databases being moved from a legacy server to our main production SQL Server cluster.  Once we did, the SQL login we had created for our application to connect to the database was unable to access the db on the destination server, despite a SQL login of the same name and password having had access on the legacy server.

At first, I was surprised, as we had done this same type of move several times during testing, moving from my development machine to a QA SQL Server, and never experienced this issue.  Upon a few moment’s further thought, we were using a different SQL login in QA than in dev.  So after moving the database from dev to QA, we had a step to update the user mapping to give it access to the database.  I didn’t think this was necessary on the production move, since the login already existed on both servers, and had access to the database on the legacy server.

Then I remembered that in the past we had experienced orphaning of some logins when moving databases between servers.  I couldn’t remember the resolution of how to resolve it, so I set out to Google and quickly found an answer I liked, on this thread:  http://dba.stackexchange.com/questions/40844/after-moving-database-backup-restore-i-have-to-re-add-user

I’ve decided to capture it here for prosperity.

First step is diagnosis–you can run this query to check for any orphaned logins in your database:

use [Your Database Name Here]
go

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name

Yes, the above query is a direct copy and paste from the article I linked.  I just wanted to have it on my own blog for easy accessibility in the future.  Full credit to Mike Fal, the first responder on that thread.

Once you’ve confirmed that the login you’re interested in displays “Orphaned!” (and yes, ours did), a very simple fix exists:

ALTER USER [LoginNameHere] WITH LOGIN=[LoginNameHere]

This re-associates the database user LoginNameHere, with the server login LoginNameHere.  After we did this step, all was good.

Advertisements

Listing explicitly granted database persmissions (SQL Server)

I recently needed to know what explicit permissions had been granted to some tables and sprocs in one of my databases, and wasn’t able to find a way to do it through the SQL Management Studio UI.  So I googled, and found this query:

explicitly granted db permissions

(Code)

T-SQL: Collapse multiple rows of SQL data into a single string

This posting may not be useful to anyone but me again in the future, but I needed to figure out how to take some text and other information from multiple rows of a table and combine them into a single string for display.

My main goal was to build a string which is a summary of a number of activities that were logged against a specific entity.  First I gathered the important activity details into a table variable defined as:

    declare @Activities table
    (
        ActivityID            uniqueidentifier,
        RegardingObjectID    uniqueidentifier,
        ActivityTypeName    nvarchar(50),
        ActivityDate        datetime,
        DescriptionText        nvarchar(max),
        CreatorId            uniqueidentifier,
        CreatorName            nvarchar(200) 
    )

Once that was populated (I’ll omit that part, as it’s pretty specific to our source system schema and not interesting for the example), I then took each of the activity entries and flattened them down into one string each in another table variable @ActivitiesStrings:

    -- Now, roll up each activity into a string representation (one per activity)
    declare @ActivitiesStrings table ( ActivityString nvarchar(max) )

    insert into @ActivitiesStrings (ActivityString)
    select '--------------------------' + char(13) + char(10) + '** ' + ActivityTypeName + 
        case when ActivityTypeName <> 'Opportunity Close' then ' added by ' else 'd by ' end +
        CreatorName + ' on ' + 
        datename(m, ActivityDate) + ' ' + datename(d, ActivityDate) + ', ' + datename(yyyy, ActivityDate) + ', ' +
        case when datepart(hh, ActivityDate) < 12 then  datename(hh, ActivityDate) else convert(nvarchar(2),datepart(hh, ActivityDate) - 12) end +
        ':' + right('00' + datename(n, ActivityDate), 2) +
        case when datepart(hh, ActivityDate) < 12 then ' AM' else ' PM' end + char(13) + char(10) + 
        'Description:' + char(13) + char(10) + 
        case when isnull(DescriptionText,'') = '' 
            then 'N/A'
            else replace(DescriptionText,  char(10), char(13) + char(10))
        end as ActivityString
    from @Activities
    order by ActivityDate desc

The char(13) + char(10) starts a new line in the system this string ultimately gets displayed in.  The —–‘s were used as visual separators for when the strings are all combined.

Next came the part I didn’t knowhow to do without looking, which was to combine these multiple activity strings into one big activity summary string.  I googled a bit and found this simple example and used it as mymy basic guide:

http://www.codeproject.com/Tips/334400/Concatenate-many-rows-into-a-single-text-string-us

My code came out as follows:

    -- finally, roll the strings all up into one big string representation of all the activity
    DECLARE @results nvarchar(max)
    SELECT @results = COALESCE(@results + char(13) + char(10) + '--------------------------' + char(13) + char(10) + char(13) + char(10) , '') + ActivityString FROM @ActivitiesStrings

    --SELECT @results  --debug

    -- and return the results
    if len(@results) > 0  
        set @results = @results + char(13) + char(10) + '--------------------------' + char(13) + char(10) + char(13) + char(10)
    else
        set @results = ''

    RETURN @results

Once again there are extra carriage returns and separator lines included to mimic how some other items are displayed in the target system showing this string.  Your milage may vary!  The return statement was included since this code is part of a scalar-valued user defined function I created for this purpose, and that’s how you return results from a UDF.


Scott’s recent tweets…