Skip to content

Archive for

SQL Server 2005 Configuration Manager not opening

You will get below error message when you try to open SQL Server 2005 Configuration Manager. Even after restarting Windows Management Instrumentation service under services.msc this errorexists.

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Invalid class [0x80041010].

How to resolve this issue:-

  • Open command prompt and go to C:\Program Files\Microsoft SQL Server\90\Shared
  • Run below command

mofcomp “C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof”

  • Now try to open SQL Server Configuration Manager and it will work fine.

Database users migration – Migrate all permissions

Here are some of the useful scripts that will be handy when you have to migrate database users alog with all the permissions to another database. You have to run all the below scripts in source database to list out the permissions, etc and the output of each script will be a new script which you cn run on target server to grant the permissions.

1) This script will list out all the database users.

SELECT 'CREATE USER [' + name + '] for login [' + name + ']' as '--Create Users--' from sys.database_principals
 where Type = 'U' and name <> 'dbo'

2) This script will list ut all the database roles granted to users.

SELECT 'EXEC sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' as '--Grant Database Roles--'
 from sys.database_principals users
 inner join sys.database_role_members link
 on link.member_principal_id = users.principal_id
 inner join sys.database_principals roles
 on roles.principal_id = link.role_principal_id

3) This script will list out all the object level permissions for each database user.

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
 + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Grant Object Level Permissions--'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC

4) This script will list out all the database level permissions for each user.

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1)
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Grant Database Level Permissions--'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC