grant view server state when transfering SQL logins

Question

I transfered the SQL logins as stated in a KB http://support.microsoft.com/kb/918992 to a new SQL server.

When I tryed to use the application which authenticates on the SQL server with a user login (login must be entered when the application is started) I got error which says I need the "view server state" permission on the SQL server. I thought all the permission were transfered when I transfered logins with the  help of the KB article?

How can I transfer the SQL logins with the server state permissions?

Answer 1

The script in the KB article does not transfer any permissions, save CONNECT SQL.

You can use this query to see which logins that were granted VIEW SERVER STATE on the old server:

SELECT sp.name
FROM   sys.server_principals sp
WHERE  EXISTS (SELECT *
               FROM  sys.server_permissions perm
               WHERE perm.grantee_principal_id = sp.principal_id
                 AND  permission_name = 'VIEW SERVER STATE')

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Great! Thanks!