April 4, 2013 at 7:00 am
Some logins been added to some server roles (bulkadmin, dbcreator etc). After running login script, user-login gets mapped and has the necessary access to db's(read, write etc) as it is in source server. But it does not have the Server roles assigned. Can we generate a script to Assign the user to a server role (as it is in source server)
April 4, 2013 at 7:27 am
SET NOCOUNT ON
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context'
-- server Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
+ QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
+ QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC
-- server level Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH ( NOLOCK )
INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ( 'S', 'U', 'G' )
ORDER BY server_principals.name,
server_permissions.state_desc,
server_permissions.permission_name
---------------------------------------------------------------------
April 4, 2013 at 7:38 am
Amazing Script Sibbald.
Using query 1 and 2, i got the script to add an user to a server role. Is it necessary to run the 3rd query - 'Grant Connect' script?
After creating logins, user-login mapping, server role add ,..an id should have exactly similar access as its in source server, isnt it?
April 4, 2013 at 7:54 am
grant connect they would have anyway but there might be other rights granted to logins in that list.
you now have everything except default language -
select 'exec sp_defaultlanguage ',+"'" +name +"'," +default_language_name from master.sys.server_principals
where type != 'R'
---------------------------------------------------------------------
April 4, 2013 at 8:39 am
Another query: http://support.microsoft.com/kb/246133
gives the 2 Stored procedure for generation of login script. But its for SQL 2000. Rev login SP has an additional code to add user to Server roles. Can that script be used for 2005 to 2008 login migration?
Or shall i use the latest one given in http://support.microsoft.com/kb/918992 which is for logins transfer between instances of 2005, 2008, 2012.
April 4, 2013 at 8:46 am
use the one for 2005 and above.
---------------------------------------------------------------------
April 4, 2013 at 9:01 am
Thanks Sibbald for the quick responses. Will get back if i still get any doubts.
April 4, 2013 at 10:35 am
What could be the reason for the below messge?
SQL auth is enabled, Login, user, db access are all provided.
Login failed for user 'ZZZZ'. Reason: Could not find a login matching the name provided.
I see
Error: 18456, Severity: 14, State: 5.
Error: 18456, Severity: 14, State: 8. (even though passwrd wasnt changed and migrated from old server using the rev login scripts)
April 5, 2013 at 3:33 am
balasach82 (4/4/2013)
What could be the reason for the below messge?SQL auth is enabled, Login, user, db access are all provided.
Login failed for user 'ZZZZ'. Reason: Could not find a login matching the name provided.
I see
Error: 18456, Severity: 14, State: 5.
Error: 18456, Severity: 14, State: 8. (even though passwrd wasnt changed and migrated from old server using the rev login scripts)
the first error can only mean what the error suggests, 'ZZZZ' is not a valid login.
As for why the password is incorrect hard to say, bur revlogin does definitely not change the password. Maybe the password is not what you think it is? Try logging on via SSMS with the id, or onto the source server.
---------------------------------------------------------------------
April 5, 2013 at 7:10 am
Error: 18456, Severity: 14, State: 5.
The login ZZZZ exists and it is linked to the user in the db. Still we get the error
>>> Login failed for user 'ZZZZ'. Reason: Could not find a login matching the name provided.
After a couple of tries from SSMS, some logins are able to connect to SSMS (not all). Change paswrd at first logon is not enabled
Error: 18456, Severity: 14, State: 8.
Since rev login is used, passwrd shdould be the same in both source and dest srvers. This is an not for one user. Its for bunch of users, so not all users would enter wrong password.
April 5, 2013 at 7:18 am
balasach82 (4/5/2013)
Error: 18456, Severity: 14, State: 5.The login ZZZZ exists and it is linked to the user in the db. Still we get the error
>>> Login failed for user 'ZZZZ'. Reason: Could not find a login matching the name provided.
After a couple of tries from SSMS, some logins are able to connect to SSMS (not all). Change paswrd at first logon is not enabled
Error: 18456, Severity: 14, State: 8.
Since rev login is used, passwrd should be the same in both source and dest srvers. This is an not for one user. Its for bunch of users, so not all users would enter wrong password.
check to see if the sids match with this query:
if they don't match, it builds the ALTER USER command to fix it.
this also builds the CREATE LOGIN commands for any missing logins, but with a default password; you probably don't need to add missing logins.
SELECT
CASE
WHEN svloginz.name is not null and dbloginz.sid <> svloginz.sid
THEN '--Login Exists but wrong sid: remap!
ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(svloginz.name) + ';'
WHEN svloginz.name is null
THEN 'CREATE LOGIN ' + quotename(dbloginz.name) + ' WITH PASSWORD=N''NotARealPassword'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;
ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(dbloginz.name) + ';'
END
from sys.database_principals dbloginz
LEFT OUTER JOIN sys.server_principals svloginz
on dbloginz.name = svloginz.name
WHERE dbloginz.type IN ('S')
AND dbloginz.name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys')
Lowell
April 5, 2013 at 8:15 am
Ran your query Lowell. I didnt get "Login Exists but wrong sid: remap!" for any login. For half of the logins, i got NULL, dont know what that means.
I checked the sid's in source and destination for all logins. all matches. sid for all the migrated logins are same.
April 8, 2013 at 6:53 am
No one faced this error 🙁
Any worthful idea which can be tried out?
May 2, 2013 at 6:00 am
After moving to 2008 from 2005, getting below error for sql logins.
Message
Login failed for user 'XXXX'. Reason: Password did not match that for the login provided.
Message
Error: 18456, Severity: 14, State: 8.
Only after changing passwords, changing to same password, for those logins, able to connect management studio. We did not chose change password at first logon
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply