/* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Procedure: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all views */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped View: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all functions */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Function: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all Foreign Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) WHILE @name is not null BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint IS NOT NULL BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint) EXEC (@SQL) PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) END GO /* Drop all Primary Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) WHILE @name IS NOT NULL BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint is not null BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint) EXEC (@SQL) PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) END GO /* Drop all tables */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Table: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) END GO
%mysql_bin%\mysql -u <username> -p USERNAME is the name of any properly permissioned MySQL administrator user. You will have to provide the password for that user. Your shell prompt should now look like this: mysql> Run this command: grant all privileges on *.* to <username>@<host FQDN or IP address> identified by "<password>"; USER is the username that you would like to give permissions for remote login. IP is the public IP address of your remote connection. PASSWORD is the password you would like to use for this username. You now must flush MySQL's privileges. Run this command: FLUSH PRIVILEGES; Run this command to exit MySQL: exit; You are done.
To uninstall MySQL and completely remove it (including all databases) from your Mac do the following: Use mysqldump to backup your databases to text files! Stop the database server
If you have ever wanted a simple script that can take a user name and role out of a prior database, and simply rename both the role and the UID, here is your code: DECLARE @DatabaseUserName [sysname] SET @DatabaseUserName = 'user_name_goes_here' SET NOCOUNT ON DECLARE @errStatement [varchar](8000), @msgStatement [varchar](8000), @DatabaseUserID [smallint], @ServerUserName [sysname], @RoleName [varchar](8000), @ObjectID [int], @ObjectName [varchar](261) SELECT @DatabaseUserID = [sysusers].[uid], @ServerUserName = [master].[dbo].[syslogins].[loginname] FROM [dbo].[sysusers] INNER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid] WHERE [sysusers].[name] = @DatabaseUserName IF @DatabaseUserID IS NULL BEGIN SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) + 'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.' RAISERROR(@errStatement, 16, 1) END ELSE BEGIN SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) + '--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) + '--Created By: ' + SUSER_NAME() + CHAR(13) + '--Add User To Database' + CHAR(13) + 'USE [' + DB_NAME() + ']' + CHAR(13) + 'EXEC [sp_grantdbaccess]' + CHAR(13) + CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) + CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) + 'GO' + CHAR(13) + '--Add User To Roles' PRINT @msgStatement DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT [name] FROM [dbo].[sysusers] WHERE [uid] IN ( SELECT [groupuid] FROM [dbo].[sysmembers] WHERE [memberuid] = @DatabaseUserID ) OPEN _sysusers FETCH NEXT FROM _sysusers INTO @RoleName WHILE @@FETCH_STATUS = 0 BEGIN SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) + CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) + CHAR(9) + '@membername = ''' + @DatabaseUserName + '''' PRINT @msgStatement FETCH NEXT FROM _sysusers INTO @RoleName END SET @msgStatement = 'GO' + CHAR(13) + '--Set Object Specific Permissions' PRINT @msgStatement DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT DISTINCT([sysobjects].[id]), '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']' FROM [dbo].[sysprotects] INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id] WHERE [sysprotects].[uid] = @DatabaseUserID OPEN _sysobjects FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName WHILE @@FETCH_STATUS = 0 BEGIN SET @msgStatement = '' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'SELECT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'INSERT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'UPDATE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'DELETE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'EXECUTE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'REFERENCES,' IF LEN(@msgStatement) > 0 BEGIN IF RIGHT(@msgStatement, 1) = ',' SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1) SET @msgStatement = 'GRANT' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName PRINT @msgStatement END SET @msgStatement = '' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'SELECT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'INSERT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'UPDATE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'DELETE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'EXECUTE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'REFERENCES,' IF LEN(@msgStatement) > 0 BEGIN IF RIGHT(@msgStatement, 1) = ',' SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1) SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName PRINT @msgStatement END FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName END CLOSE _sysobjects DEALLOCATE _sysobjects PRINT 'GO' END