Sql Server Error : An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

Interesting one today:

Occasionally, when we restore databases from backups, we need to fix orphan users. Recently, a few years ago, we discussed about fixing orphan users using script. Here we’ll look at a scenario, where the regular orphan users script will not work. 

Error Message:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214 [Batch Start Line 61]
An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.

The script “EXEC sp_change_users_login 'Auto_Fix', 'user'” only works for Sql Authentication based logins and not for Windows-level principals or non-login based users. See MSDN article for more details.

A user without login would look like this, in SSMS:

Sql Users Without Login

In such scenarios,

• sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
• sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : View or function has more column names specified than columns defined

Interesting one today:

In our development environment, sometimes we run into this error that’s a bit nebulous.

Error Message:
SQLException for SQL []; SQL state [S0001]; error code [4502]; View or function has more
column names specified than columns defined.;
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:
View or function has more column names specified than columns defined.

Occasionally, we make changes to the tables schema and when you have Views that depend on these tables, they become stale. While the definition is still intact, the meta-data becomes stale, so they need to be refreshed.

This refresh uncovers if there are any conflicts between view definitions and the underlying tables. 

--
-- Refresh View metadata 
--
EXEC sp_refreshview 'dbo.View_SomeTable'
GO 

Once the underlying conflicts are resolved, if any, the view will execute correctly from application. Just refreshing the meta-data helps sometimes.

 

Hope this helps,

_Sqltimes

Read Full Post »